New server proxy
SQL Server Agent is a job scheduling agent that ships with SQL Server. Its infrastructure consists of a Windows service that is used to execute tasks (called jobs in SQL Server parlance), and a set of SQL Server tables that house the metadata about these jobs. The agent can execute numerous types of jobs, either on a schedule, or on-demand. In this article, we will look at some of the best practices for security that should be followed when installing and setting up SQL Server Agent. I’m assuming you already have a working knowledge of configuring and using SQL Server Agent.
Who sees what
A common request that DBAs get is to grant the development team access to SQL Server Agent. As with all database access, you should grant only as much access as required, because of the inherent security risks of a task scheduler.
SQL Server contains 3 fixed database roles on the MSDB database, which gives administrators fine control over access to SQL Server Agent. The SQL Server Agent node in SSMS is visible only to users in one of these 3 roles (except sysadmins, who can see everything irrespective of role membership). Here is an explanation of the roles, in order from the most restrictive to least restrictive:
- SQLAgentUserRole – Users in this role are granted view/edit/delete/execute access to only jobs owned by them. Users in this role cannot view any jobs owned by system administrators, or by users in the other two roles. Grant this role when you want users to only see jobs owned by them.
- SQLAgentReaderRole – Users in this role get all the privileges of theSQLAgentUserRole, i.e. they get access to owned jobs. In addition to that, they can also view (but not modify or execute) all jobs on SQL Server Agent, irrespective of ownership. Grant this role when you want users to be able to view, but not execute, all jobs in the system, but modify/execute only jobs owned by them.
- SQLAgentOperatorRole – Users in this role get all the privileges of the SQLAgentReaderRole. In addition to that, they can also execute, or enable/disable any job in the system. However, users in this role can modify only owned jobs. Grant this role for super users who can view/execute all jobs on the system.
Always start by granting users the most restrictive role – SQLAgentUserRole, and upgrade membership to higher roles if required. Use the below table to determine the type of access you should grant:Action SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole Create/modify/delete Only owned jobs Only owned jobs Only owned jobs View List Only owned jobs All jobs All jobs Enable/Disable Only owned jobs Only owned jobs All jobs View Properties Only owned jobs All jobs All jobs Edit Properties Only owned jobs Only owned jobs Only owned jobs Start / Stop Only owned jobs Only owned jobs All jobs View job history Only owned jobs All jobs All jobs Delete job history No No Only owned jobs Change Ownership No No No