SQL Server Agent Fixed Database Roles

A login must have authority over some database roles in the msdb database in order to access SQL Server Agent. These roles in the msdb database are as follows.

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

If a login is authorized in any of the above database roles or if it is sysadmin, it can see SQL Server Agent on SSMS. Let’s explain these roles.

SQLAgentUserRole: This role is the least authorized role of the SQL Server Agent database roles. The following table that I have copy from msdn shows the operations that a login with this role can do. For detailed information about operators and proxies, please refer to the following articles.

How To Create An Operator On SQL Server“,

Run Your Jobs With a Proxy Account

Action Operators Local jobs

(owned jobs only)

Job schedules

(owned schedules only)

Proxies
Create/modify/delete No Yes

Cannot change job ownership.

Yes No
View list (enumerate) Yes

Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.

Yes Yes Yes

List of proxies only available in the Job Step Propertiesdialog box of Management Studio.

Enable/disable No Yes Yes Not applicable
View properties No Yes Yes No
Execute/stop/start Not applicable Yes Not applicable Not applicable
View job history Not applicable Yes Not applicable Not applicable
Delete job history Not applicable No

Members of SQLAgentUserRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs.

Not applicable Not applicable
Attach/detach Not applicable Not applicable Yes Not applicable

SQLAgentReaderRole: This role has the full authority of SQLAgentUserRole. There are some extra privileges. You can see the details in the following table I copied from msdn.

Action Operators Local jobs Multiserver jobs Job schedules Proxies
Create/modify/delete No Yes (owned jobs only)

Cannot change job ownership.

No Yes (owned schedules only) No
View list (enumerate) Yes

Can get list of available operators for use in sp_notify_operator and the Job Propertiesdialog box of Management Studio.

Yes Yes Yes Yes

List of proxies only available in the Job Step Propertiesdialog box of Management Studio.

Enable/disable No Yes (owned jobs only) No Yes (owned schedules only) Not applicable
View properties No Yes Yes Yes No
Edit properties No Yes (owned jobs only) No Yes (owned schedules only) No
Execute/stop/start Not applicable Yes (owned jobs only) No Not applicable Not applicable
View job history Not applicable Yes Yes Not applicable Not applicable
Delete job history Not applicable No

Members of SQLAgentReaderRolemust explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs.

No Not applicable Not applicable
Attach/detach Not applicable Not applicable Not applicable Yes (owned schedules only) Not applicable

SQLAgentOperatorRole: This role has the full authority of SQLAgentReaderRole. There are some extra privileges.

You can see the details in the following table I copied from msdn.

Action Alerts Operators Local jobs Multiserver jobs Job schedules Proxies
Create/modify/delete No No Yes (owned jobs only)

Cannot change job ownership.

No Yes (owned schedules only) No
View list (enumerate) Yes Yes

Can get list of available operators for use in sp_notify_operatorand the Job Properties dialog box of Management Studio.

Yes Yes Yes Yes
Enable/disable No No Yes

SQLAgentOperatorRolemembers can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the **@enabled** and the **@job_id** (or **@job_name**) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.

No Yes

SQLAgentOperatorRolemembers can enable or disable schedules they do not own by using the stored procedure sp_update_scheduleand specifying values for the **@enabled** and the **@schedule_id** (or **@name**) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.

Not applicable
View properties Yes Yes Yes Yes Yes Yes
Edit properties No No Yes (owned jobs only) No Yes (owned schedules only) No
Execute/stop/start Not applicable Not applicable Yes No Not applicable Not applicable
View job history Not applicable Not applicable Yes Yes Not applicable Not applicable
Delete job history Not applicable Not applicable Yes No Not applicable Not applicable
Attach/detach Not applicable Not applicable Not applicable Not applicable Yes (owned schedules only) Not applicable

Source: https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-2017

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *