SQL Server Agent Fixed Database Roles

 

SQL Server has the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:

  • SQLAgentUserRole

  • SQLAgentReaderRole

  • SQLAgentOperatorRole

When users who are not members of one of these roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent.

The SQL Server Agent database role permissions are concentric in relation to one another -- more privileged roles inherit the permissions of less privileged roles on SQL Server Agent objects (including alerts, operators, jobs, schedules, and proxies). For example, if members of least-privileged SQLAgentUserRole have been granted access to proxy_A, members of both SQLAgentReaderRole and SQLAgentOperatorRole automatically have access to this proxy even though access to proxy_A has not been explicitly granted to them. This may have security implications, which are discussed in the following sections about each role.

SQLAgentUserRole Permissions

SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole.

System_CAPS_ICON_important.jpg Important


Consider the security implications before granting proxy access to members of theSQL ServerAgentdatabaseroles. The SQLAgentReaderRole and the SQLAgentOperatorRole are automatically members of the SQLAgentUserRole. This means that members of SQLAgentReaderRole and SQLAgentOperatorRole have access to all SQL Server Agent proxies that have been granted to the SQLAgentUserRole and can use those proxies.

The following table summarizes SQLAgentUserRole permissions on SQL Server Agent objects.

ActionOperatorsLocal jobs

(owned jobs only)
Job schedules

(owned schedules only)
Proxies
Create/modify/deleteNoYes

Cannot change job ownership.
YesNo
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.
YesYesYes

List of proxies only available in the Job Step Properties dialog box of Management Studio.
Enable/disableNoYesYesNot applicable
View propertiesNoYesYesNo
Execute/stop/startNot applicableYesNot applicableNot applicable
View job historyNot applicableYesNot applicableNot applicable
Delete job historyNot applicableNo

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 applicableNot applicable
Attach/detachNot applicableNot applicableYesNot applicable

SQLAgentReaderRole Permissions

SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.

System_CAPS_ICON_important.jpg Important


Consider the security implications before granting proxy access to members of theSQL ServerAgentdatabaseroles. Members of SQLAgentReaderRole are automatically members of the SQLAgentUserRole. This means that members of SQLAgentReaderRole have access to all SQL Server Agent proxies that have been granted to SQLAgentUserRole and can use those proxies.

The following table summarizes SQLAgentReaderRole permissions on SQL Server Agent objects.

ActionOperatorsLocal jobsMultiserver jobsJob schedulesProxies
Create/modify/deleteNoYes (owned jobs only)

Cannot change job ownership.
NoYes (owned schedules only)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.
YesYesYesYes

List of proxies only available in the Job Step Properties dialog box of Management Studio.
Enable/disableNoYes (owned jobs only)NoYes (owned schedules only)Not applicable
View propertiesNoYesYesYesNo
Edit propertiesNoYes (owned jobs only)NoYes (owned schedules only)No
Execute/stop/startNot applicableYes (owned jobs only)NoNot applicableNot applicable
View job historyNot applicableYesYesNot applicableNot applicable
Delete job historyNot applicableNo

Members of SQLAgentReaderRole 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.
NoNot applicableNot applicable
Attach/detachNot applicableNot applicableNot applicableYes (owned schedules only)Not applicable

SQLAgentOperatorRole Permissions

SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.

SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server. To enable or disable local jobs or schedules, members of this role must use the stored procedures sp_update_job and sp_update_schedule. Only the parameters that specify the job or schedule name or identifier and the @enabled parameter can be specified by members of SQLAgentOperatorRole. If they specify any other parameters, execution of these stored procedures fails. SQLAgentOperatorRole members cannot change job ownership to gain access to jobs that they do not already own.

The Jobs, Alerts, Operators, and Proxies nodes in SQL Server Management Studio Object Explorer are visible to members of SQLAgentOperatorRole. Only the Error Logs node is not visible to members of this role.

System_CAPS_ICON_important.jpg Important


Consider the security implications before granting proxy access to members of theSQL ServerAgentdatabaseroles. Members of SQLAgentOperatorRole are automatically members of SQLAgentUserRole and SQLAgentReaderRole. This means that members of SQLAgentOperatorRole have access to all SQL Server Agent proxies that have been granted to either SQLAgentUserRole or SQLAgentReaderRole and can use those proxies.

The following table summarizes SQLAgentOperatorRole permissions on SQL Server Agent objects.

ActionAlertsOperatorsLocal jobsMultiserver jobsJob schedulesProxies
Create/modify/deleteNoNoYes (owned jobs only)

Cannot change job ownership.
NoYes (owned schedules only)No
View list (enumerate)YesYes

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

SQLAgentOperatorRole members 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.
NoYes

SQLAgentOperatorRole members can enable or disable schedules they do not own by using the stored procedure sp_update_schedule and 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 propertiesYesYesYesYesYesYes
Edit propertiesNoNoYes (owned jobs only)NoYes (owned schedules only)No
Execute/stop/startNot applicableNot applicableYesNoNot applicableNot applicable
View job historyNot applicableNot applicableYesYesNot applicableNot applicable
Delete job historyNot applicableNot applicableYesNoNot applicableNot applicable
Attach/detachNot applicableNot applicableNot applicableNot applicableYes (owned schedules only)Not applicable

Members of the sysadmin fixed server role have access to all SQL Server Agent functionality. If a user is not a member of the sysadmin role, but is a member of more than one SQL Server Agent fixed database role, it is important to remember the concentric permissions model of these roles. Because more privileged roles always contain all the permissions of less privileged roles, a user who is a member of more than one role automatically has the permissions associated with the most privileged role that the user is a member of.

Implement SQL Server Agent Security
sp_update_job (Transact-SQL)
sp_update_schedule (Transact-SQL)
sp_notify_operator (Transact-SQL)
sp_purge_jobhistory (Transact-SQL)

Community Additions

ADD
Show: