Security for SQL Server Agent Administration
SQL Server Agent introduces the SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole fixed database roles in the msdb database that control access to SQL Server Agent for users who are not members of the sysadmin fixed server role. In addition to these fixed database roles, subsystems and proxies help database administrators ensure that each job step runs with the minimum permissions required to perform its task.
Roles
Members of the SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole fixed database roles in msdb, and members of the sysadmin fixed server role have access to SQL Server Agent. A user that does not belong to any of these roles cannot use SQL Server Agent. For more information on the roles used by SQL Server Agent, see Implementing SQL Server Agent Security.
Subsystems
A subsystem is a predefined object that represents functionality that is available to a job step. For more information, see SQL Server Agent Subsystems.
Proxies
SQL Server Agent uses proxies to manage security contexts. A proxy can be used in more than one job step. Members of the sysadmin fixed server role can create proxies.
Each proxy corresponds to a security credential. Each proxy can be associated with a set of subsystems and a set of logins. The proxy can be used only for job steps that use a subsystem associated with the proxy. To create a job step that uses a specific proxy, the job owner must either use a login associated with that proxy or be a member of a role with unrestricted access to proxies. Members of the sysadmin fixed server role have unrestricted access to proxies. Members of SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole can only use proxies to which they have been granted specific access. Each user that is a member of any of these SQL Server Agent fixed database roles must be granted access to specific proxies so that the user can create job steps that use those proxies.
See Also
Concepts
Creating SQL Server Agent Proxies
Selecting an Account for the SQL Server Agent Service
Implementing SQL Server Agent Security
SQL Server Agent Fixed Database Roles
Other Resources
CREATE CREDENTIAL (Transact-SQL)
sp_grant_login_to_proxy (Transact-SQL)
sp_revoke_login_from_proxy (Transact-SQL)