How to assign a user with viewing jobs privilege only?

Question

How to assign a user with viewing jobs privilege only?

Chaohua Wang-372299

SSC Veteran

Points: 209
More actions
October 4, 2006 at 12:45 am

#367518

Thanks in advance.

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Do you want the user to be able to see the properties of every job or just see the list of jobs? Depending on what exactly you want to allow, you would need to grant EXECUTE on some or all of the following stored procedures in msdb:

sp_help_job

sp_help_jobhistory

sp_help_jobschedule

sp_help_jobstep

Greg

Chaohua Wang-372299 SSC Veteran Points: 209 More actions · Answer 2

Not really. I can easily assign read privilege to a user. The, the user can retrieve all information from the system database. However, it is not enough for an inexperience DBA developer. I prefer to allow the uesr to view jobs from the Enterprise Manager.

Actually, I can do it if I only need to do it for one user. I am confused if I can do it for more than one user with different viewing privilege.

Any input will be greatly appreciated.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

Enterprise Manager uses the system stored procedures in msdb. I verified this by starting a Profiler trace and using Enterprise Manager to expand Management -> SQL Server Agent -> Jobs. Then I looked at the trace to see what was executed. It showed sp_help_job and sp_help_catagory being executed.

If you want to allow a group to see jobs, I suggest you create a database role in msdb, grant it the permissions I listed, and add users to it. If you want all users to be able to see jobs, use the public role and guest user.

Greg