Post reply

Access to View SQL jobs

  • January 29, 2004 at 4:21 pm

    #84142

    I would like to grant some of the developers access to see the scheduled jobs under SQL Agent, without granting them permissions to create or delete jobs. I made changes to our security to not to allow anyone to create DTS packages or jobs, following standard SQL Server security guideleines posted at this site. Only sysadmins can create DTSs or jobs. However I would like the grant the developers ability to view ALL the jobs, since some of them would like to check when jobs fail and do some troubleshooting. Right now we do not have any monitoring tool to monitor the jobs, until we have that we have to let some developers do the monitoring. The key is that all the jobs are scheduled under sa account, and with the security changes I did each developer can only see their job. Is there any way to let them see all the jobs? I might be OK with letting them run the jobs, but not change them.

    Thanks

  • January 30, 2004 at 8:06 am

    #492344

    The TargetServersRole role has the ability to view the jobs, but I think they'd also have permission to stop/start jobs. TargetServersRole is an undocumented role used for Multi-Server administration, so any use of it is obviously unsupported by MS. There have been some changes to what it can do across service packs, so you probably need to test in your environment and see if it has the permissions you need.

     

    K. Brian Kelley
    @kbriankelley

  • January 30, 2004 at 11:38 am

    #492386

    Trace yourself in profiler while you look at the jobs.. That should give you an idea of the permissions you need.   Without testing anything I am guess readonly permissions in msdb will do the trick.


    "Keep Your Stick On the Ice" ..Red Green

  • January 30, 2004 at 1:11 pm

    #492397

    Just add a little to Brian's reply, TargetServersRole has been denied to run most job related storoed procedures in server pack 3.

  • February 3, 2004 at 7:51 pm

    #492694

    TargetServer role did  the trick. Once I add the users to this group they can see all the jobs. Now I can selectively grant exec permissions on sp_startjob to users who Iwant to be able to run the jobs.

    Thanks for an elegant solution

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

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