SQL Server Agent Security Overview

The Agent security enhancements in SQL Server 2005 include the two new database roles in the msdb system database and an ability to use multiple proxy accounts. Administrators can use the new database role named SQLAgentUserRole to manage users who can create or execute SQL Server agent jobs. By default, no user is a member of the SQLAgentUserRole role.

Note

Except for members of the sysadmin server role, users are not able to see the SQL Server Agent folder in Object Explorer in Management Studio unless they are part of the SQLAgentUserRole msdb database role.

SQL Server 2000 allowed one proxy account. A proxy essentially defines the security context for a job step. SQL Server changes that by allowing you to have any number of proxy accounts. You as a system administrator can allow logins, msdb roles, and system roles to access one or more proxy accounts. You can also assign proxy accounts to access one or more subsystems such as SQL Server Integration Services (SSIS; formerly DTS) package execution, Replication Distributor, Replication Transaction Log Reader, and so on. Members of the sysadmin server role can use the sp_add_proxy stored procedure to create a new SQL Agent proxy account and sp_grant_login_to_proxy to grant access on a proxy to a principal.