Granting access to the Job Activity Monitor

Question

I'm trying to grant access to the Job Activity Monitor. On my model office server I can create a new login and add it to the SQLAgentReaderRole role in msdb, and the id has access to the monitor. However in my production environment the exact same process gives me the following error:

Both are 2008 R2, but production is SP2, and MO is SP3. I'm making the assumption this doesn't matter but I could be wrong.

I checked and the associated permissions (SQLAgentReaderRole, SQLAgentUserRole, PUBLIC in msdb and master) and they appear to be the same.

Answer 1

In order to give some privileged users access to xp_sqlagent_enum_jobs we did the following.

1. Create a Database Role in master named something like SQLAgentJobManager.
2. Granted that role EXECUTE rights on xp_sqlagent_enum_jobs.

Then via Logins or a Domain group, granted users a membership in the SQLAgentJobManager role in master.

EDIT: The way we implement this permission is through Domain Group login(s) such as (SQLAgentJobManagerGroup). Then add the group login to master's SQLAgentJobManager role (for running xp_sqlagent_enum_jobs) and to msdb's SQLAgentReaderRole or SQLAgentUserRole.

After that we always add or remove logins from the SQLAgentJobManagerGroup to control the group membership for those rights.