2

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:

Screenshot of error message

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.

3

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.

  • I may have to go that route. I was hoping to avoid it. Mostly at this point I want to figure out why on earth it works on one instance but not another. – Kenneth Fisher Aug 7 '15 at 21:34
  • Is your other instance an older edition that was upgraded in place sometime ago? – RLF Aug 7 '15 at 21:48
  • Nope. Both are fresh installs (well awhile ago anyway). – Kenneth Fisher Aug 9 '15 at 2:45
  • Are the trustworthy settings the same on both servers? – RLF Aug 15 '15 at 19:42
  • Yes. msdb and all of the system databases have the same (default) settings. msdb is trustworthy and the other three are not. – Kenneth Fisher Aug 17 '15 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.