Job Permissions

  • I want my user to be able to view, edit, run, etc. the jobs on a database server. What permission(s) do I need to give him? Thanks in advance.

  • I just read in the BOL that the execute permissions on jobs default to public. However, my user is not able to see the jobs at all. What is going on? Thanks.

  • I have users with less privilages who can see the jobs. This is really bugging me, please help. Thank you.

  • Though sp_start_job defaults to the public role, a user needs to be able to see the job in order to start it. A user can normally see a job if one of the two are true:

    (1) User is a member of the sysadmin role.

    (2) User owns the job.

    However, in order to support MSX, there is the TargetServersRole in the msdb database. Users who are made members of this role can see the jobs and execute sp_start_job.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • Adding a user to TargetServerRole allows them to edit job stops, delete jobs, run jobs they don't own, and other activities to support MSX.

    It was a potential security issue to allow members of that role to be able to execute jobs, so it is tightened down (denied) by SQL Server 2000 service pack 3.

    For any installation before service pack 3, you are still able to view/edit/execute jobs owned by someone else.

  • Thanks a lot for your help guys. I have given the role to the user and now he is able to see the jobs. What gets me is that my other users who are members of the public role in various databases are able to see the jobs...what am I missing here?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply