Problem : In most of your projects, there is always that situation that the application team have that need to run their own application jobs in the SQL Agent. But how do you allow them to let them have that right (and fun) without letting them make any changes to the SQL Agent jobs?
Solution : DBA can allow this by granting them certain permissions and making them members of any 3 SQL Agent database roles in the system database msdb.
SQL Agent fixed database roles
There are 3 SQL Agent fixed database roles in msdb namely;
- SQLAgentOperatorRole – Of all the 3 fixed database roles, this is the most privileged role. Members of this role can manage and run jobs that are owned by them or owned by other accounts. They also have access and enumerate proxies and alerts and delete job history. They automatically inherit the privileges of SQLAgentReaderRole and SQLAgentUserRole.
- SQLAgentReaderRole – Members of this role can list all multi server jobs, their properties and their history. and not those jobs and schedules that they own. Members of this role automatically inherit the permissions for SQLAgentUserRole.
- SQLAgentUserRole – Members of this role has the least privilege of the 3 fixed roles. They are allowed to view local jobs, operators and schedules. They only have access to local jobs that they own.
Aside from making the App team members of any of the 3 fixed database roles in msdb database, you will need to grant them permission to execute in some Agent extended stored procedures.
To check if the Agent extended procedures are enabled in your system,
select value, value_in_use from sys.configurations where name = ‘Agent XPs’
The Agent extended procedures that I needed to grant them viewing of the Agent jobs and schedules under SQL Agent menu in SSMS are the following.
grant select on master.dbo.sysperfinfo to [YourAppTeamGroup]
grant execute on master.dbo.xp_sqlagent_notify to [YourAppTeamGroup]
grant execute on master.dbo.xp_sqlagent_enum_jobs to [YourAppTeamGroup]
grant execute on master.dbo.xp_sqlagent_param to [YourAppTeamGroup]
grant execute on master.dbo.xp_sqlagent_is_starting to [YourAppTeamGroup]
grant execute on master.dbo.xp_instance_regenumvalues to [YourAppTeamGroup]
After executing the above statement, your App team should be able to see the SQL Agent GUI, view their jobs and job properties, but not make any changes in them.
Now, you have less complains and you made the App team happy!