Implementing SQL Server Agent Security
SQL Server Agent lets the database administrator run each job step in a security context that has only the permissions required to perform that job step, which is determined by a SQL Server Agent proxy. To set the permissions for a particular job step, you create a proxy that has the required permissions and then assign that proxy to the job step. A proxy can be specified for more than one job step. For job steps that require the same permissions, you use the same proxy.
Note
After upgrading from SQL Server 2000, all user proxy accounts that existed before upgrading are changed to the temporary global proxy account UpgradedProxyAccount. UpgradedProxyAccount is only granted access to those subsystems that were explicitly used, and does not have access to all subsystems after upgrading.
The following section explains what database role you must grant to users so they can create or execute jobs by using SQL Server Agent.
Granting Access to SQL Server Agent
To use SQL Server Agent, users must be a member of one or more of the following fixed database roles:
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
These roles are stored in the msdb database. By default, no user is a member of these database roles. Membership in these roles must be granted explicitly. Users who are members of the sysadmin fixed server role have full access to SQL Server Agent, and do not need to be a member of these fixed database roles to use SQL Server Agent. If a user is not a member of one of these database roles or of the sysadmin role, the SQL Server Agent node is not available to them when they connect to SQL Server by using SQL Server Management Studio.
Members of these database roles can view and execute jobs that they own, and create job steps that run as an existing proxy account. For more information about the specific permissions that are associated with each of these roles, see SQL Server Agent Fixed Database Roles.
Members of the sysadmin fixed server role have permission to create, modify, and delete proxy accounts. Members of the sysadmin role have permission to create job steps that do not specify a proxy, but instead run as the SQL Server Agent service account, which is the account that is used to start SQL Server Agent.
Guidelines
Follow these guidelines to improve the security of your SQL Server Agent implementation:
Create dedicated user accounts specifically for proxies, and only use these proxy user accounts for running job steps.
Only grant the necessary permissions to proxy user accounts. Grant only those permissions actually required to run the job steps that are assigned to a given proxy account.
Do not run the SQL Server Agent service under a Microsoft Windows account that is a member of the Windows Administrators group.