SQL Agent Security

SQL Agent security is more fine-grained than ever. In this section, we cover not only the service account but new security around who can create, see, and run SQL Agent jobs. What security do job steps run under? In SQL 2000, there was only the SQL Agent Service account and a single proxy account. SQL Server 2005 expands this to allow multiple, separate proxy accounts to be affiliated with each job step. These proxy accounts are associated with SQL logins. This provides excellent control for each type of job step.

Service Account

The SQL Server Agent service account should be a domain account if you plan on taking advantage of Database Mail or require any network connectivity. The account should map to a login that is also a member of the sysadm fixed server role.

Access to SQL Agent

After the installation, only members of the sysadm fixed server role have access to SQL Server Agent objects. Others will not even see the SQL Agent object in the Object Explorer of Management Studio. To allow other users access to SQL Agent, you must add them to one of three fixed database roles in msdb database:

• SQLAgentUserRole

• SQLAgentReaderRole

• SQLAgentOperatorRole

These are listed in order of increased capability. Each higher role includes the permissions associated with the lower roles. Therefore, it does not make sense to assign a user to more than one role.

Note

Members of the sysadm fixed server role have access to all of the capabilities of SQL Agent and do not have to be added to any of these roles.

SQLAgentUserRole

Members of the user role have the most restricted access to SQL Agent. They can only see the Jobs node under SQL Agent and can only have access to local jobs and schedules that they own. They cannot use multiserver jobs, which are discussed later in this chapter. They can create, alter, delete, execute, start, and stop their own jobs and job schedules. They can view but not delete the job history for their own jobs. They can see and select operators to be notified on completion of their jobs and choose from the available proxies for their job steps.

SQLAgentReaderRole

The reader role includes all of the permissions of the user role. It can create and run the same things as a user, but this role can see the list of multiserver jobs, their properties, and history. They can also see all of the jobs and schedules on the local server, not just the ones they own. They can only see the Jobs node under SQL Agent as well.

SQLAgentOperatorRole

The operator role is the least restricted role and includes all of the permissions of the reader role and the user role. This role has additional read capabilities as well as execute capabilities. Members of this role can view the properties of proxies and operators. They can list the available proxies and alerts on the server as well.

Members of this role can also execute, start, or stop local jobs. They can enable or disable any job or operator, although they must use the sp_update_job and sp_update_schedule procedures to do so. They can delete job history for any job. Jobs, Alerts, Operators, and Proxies nodes under SQL Agent are visible to this role. Only the Error Log node is hidden.

Proxy Accounts

Job-step security has been completely redone in SQL Server 2005 to be much more granular. In SQL Server 2000, sysadm role members executed job steps under the context of the SQL Agent Service Account. SQL 2000 allowed you to set up one other proxy account, whose permission applied to all other users.

SQL Agent Subsystems

In SQL Server 2000, all job steps that accessed non-SQL resources (Active Script and Command Exec steps) operated under a single permission structure for everyone. The first step to making security finer grained is to break up the objects on which security can be defined. In SQL 2005, security can be placed on each SQL Agent subsystem. There are 11. In the order they appear when you are adding a job step, they are:

• ActiveX Script

• Operating System (CmdExec)

• Replication Distributor

• Replication Merge

• Replication Queue Reader

• Replication Snapshot

• Replication Transaction Log Reader

• Analysis Services Command

• Analysis Services Query

• SSIS Package Execution

• Transact SQL

The permissions for Transact SQL are not governed by proxy. Each user executes T-SQL under his own account. If you are a member of the sysadm group, you can choose any SQL login as the Run As Account.

All of the other subsystems use one or more proxies to determine permissions for the subsystem.

Proxies

Figure 5-7 shows the basic relationship among the parts.

Figure 5-7

Each subsystem has its own permissions. Our example shows the setup for permissions for the operating system (CmdExec) subsystem. The issue is, what operating-system permissions are used when someone executes a CmdExec job step? How can we allow multiple levels of permissions for different groups of users?

The proxy combines the permissions for the CmdExec step, as well as the users who may run under this proxy.

Credentials

The first thing you must do is create a credential. The easiest way to create a credential is in Management Studio: Expand Security, right-click on Credentials, and choose New Credential. You will be presented with a dialog box like the one shown in Figure 5-8.

Figure 5-8

Give the credential a friendly name, and associate it with a Windows login or group. You must also provide the password to complete the creation. The permissions associated with this login or group will be the permissions applied to the CmdExec job step.

Note

If your passwords time out on a regular basis, your job steps will begin to fail. You will have to reset the passwords for each credential or increase or drop the password expiration for the special accounts. These accounts should be created specifically for this and have the minimum security necessary for the job step to complete successfully.

Create Proxy

Now you can create your proxy. In Management Studio, expand SQL Agent, right-click Proxy, and choose New Proxy. You will get a New Proxy Account dialog box, shown in Figure 5-9.

Figure 5-9

Give the Proxy a name that provides information about its security level or its intended use. Then associate a credential with the proxy. The proxy will provide the permissions associated with its credential when it is used. Provide a more detailed description of what the proxy allows and how it should be used and when.

Then select the subsystems that can use the proxy. A proxy can be associated with many subsystems. Then you create a list of users (principles) who may use this proxy. This is done on the Principles page. A principle can be a Server Role, a SQL Login, or an msdb role.

Note

If you upgraded from SQL 2000, the proxy information from SQL Agent was placed in a SQL 2005 proxy named UpgradedProxyAccount. Although this account should be temporary, your jobs will continue to run as they did in SQL 2000. This proxy account is only associated with the subsystems actually used in the SQL 2000 jobs, not all subsystems. You should create SQL 2005 proxies and replace this proxy.

Using Proxies

Now assume we have created the two proxies for the CmdExec subsystem as Figure 5-7 describes. Your SQL login is associated with both proxies. You wish to create a job that contains a CmdExec job step. When you add the job step, open the drop-down labeled Run as: This contains a list of all of the proxies you are allowed to use for your job step. Each proxy has its own permissions. Choose the proxy that contains the permissions you need for your job step, and you should be ready to go.