In order to give some privileged users access to xp_sqlagent_enum_jobs
we did the following.
- Create a Database Role in
master
named something likeSQLAgentJobManager
. - Granted that role
EXECUTE
rights onxp_sqlagent_enum_jobs
.
Then via Logins or a Domain group, granted users a membership in the SQLAgentJobManager
role in master
. Works
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 usthose rights.