In my earlier article, SQL Server Agent – General Configuration, I discussed the SQL Server Agent service account and how the user you choose and its permissions can affect your data security. I want to discuss SQL Agent security a little deeper because it can be difficult to understand all the security options the DBA has available for SQL Agent. Any time security configuration is difficult to understand it is easy to expose data unintentionally.
SQL Server Agent Database Roles
Of course, the sysadmin server role allows its members complete access to all SQL Agent functionality. Additionally, there are three database roles in msdb that grant users varying levels of access to create and execute jobs. They are (in order of least to most privileged):
Users who are not a sysadmin or a member of one of these three roles have no access to SQL Server Agent on the server. It is important to remember that users inherit the permissions of less privileged roles on Agent objects.
This means you need to be aware that if you grant access to a proxy to the SQLAgentUserRole all other SQL Agent roles will have access to the proxy as well, which may not be something you intended.
Agent Role Permissions
Each SQL Agent role has a specific set of permissions and you can find a full list on MSDN. Here are some highlights:
- Create/Modify/Delete/View his own jobs and schedules
- View a list of Operators and Proxies (in Job Properties window)
- Start/Stop his own jobs
- All SQLAgentUserRole permissions
- View a list of all jobs and job history on server
- All SQLAgentReaderRole permissions
- View all Alerts, Operators, Jobs, Schedules, Proxies and the properties for those objects
- Start/Stop any job
- Delete job history
Jobs must be owned by a Login that is not a Windows Group. Also, members of any SQL Agent database role cannot change job ownership even if he/she can view the job. The consequence of SQL Agent’s ownership functionality is that database development teams that are not in the sysadmin server role are must rely on a single Login for job ownership.
If the team’s jobs are owned by a Windows account the team is dependent on that one person for all job administration. Typically, a team will use a shared SQL Login for job ownership to avoid the maintenance challenges associated with the Windows account. It is important for the DBA to keep this fact in mind when assigning permissions, particularity proxies, to avoid exposing resources unintentionally.
Another note about job ownership, T-SQL tasks run under non sysadmin logins are always run under the permissions of the job owner. This often creates a scenario where the developer creates a working query that fails when run in a job step, the job owner may not have the same permission as the developer.
SQL Agent Proxies
A SQL Agent Proxy allows a job step to be run under a Windows user that is not the job owner or the SQL Agent service account. Proxies can be assigned to multiple SQL Agent subsystems (PowerShell, Replication, Analysis Services, Integration Services, etc.). SQL Agent will impersonate the configured proxy user when the job step configured to use the proxy is executed.
The Windows user credentials are stored in SQL Server. If your proxy’s credentials have expiring passwords the DBA must maintain those proxy credentials appropriately or the job will fail.
You should consider using highly restricted Windows users for proxies. The Windows user should have access to only those resources needed to complete the job step and nothing more. It is easy to grant access to a variety of resources to a Windows user used in a proxy and forget later how much access that proxy may have to data and network resources. Those resources are then available to any database user that may have access to the proxy, possibly unintentionally.
Configuring a proxy for use takes multiple steps, you must:
- Create the proxy
- Assign it to one or more SQL Agent subsystems
- Grant principals (SQL Login, msdb roles or server roles) access to the proxy
- Configure a job step to use the proxy
You can get detailed proxy instructions on Books Online.