Access to View SQL jobs

Question

Access to View SQL jobs

mssql_rules

SSCrazy

Points: 2783
More actions
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

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

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

Jeff W SSCertifiable Points: 5207 More actions · Answer 2

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

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

mssql_rules SSCrazy Points: 2783 More actions · Answer 4

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