In the previous level of this Stairway, you learned how to launch external programs from within SQL Server Agent job steps. You can use the outdated ActiveX system, run batch command scripts from a virtual command prompt, or even launch your own programs. Your best option is to use the PowerShell subsystem to run PowerShell scripts. PowerShell scripts will allow you to manipulate nearly all aspects of Windows and SQL Server. In this level, you will dig into SQL Server Agent security. Security can be a confusing topic to many, and deserves some explicit consideration. There are two distinct aspects of security that will be examined in this series: the security to run SQL Server Agent jobs, and the impersonation and security accounts that can be used as proxy accounts to run job steps. The next level in the series will deal with proxy accounts, this level will focus on security rights to run SQL Server Agent and SQL Server Agent Jobs.

Security Requirements for the SQL Server Agent Service Account

Choosing a SQL Server Agent service account was covered in Level 1 of the series, but you will revisit the decision now based upon your deeper understanding of how SQL Server Agent works. If you intend to have jobs connect back only to your local SQL Server instance, then minimal permissions are needed for the SQL Server Agent service account. NetworkService is a good choice for the service account in this case. With Windows Server 2008 R2 and SQL Server 2008 R2, this will provide a very secure account that can easily be granted the rights to work with SQL Server.

If you intend to use some of the more advanced features of SQL Server Agent, such as shelling out using the CmdExec subsystem or the PowerShell subsystem, or if you intend to connect to remote SQL Server instances or network shares, you will likely want a custom service account using a Windows Domain user account. You can create an account specifically for SQL Server Agent, using either a common account for all SQL Server Agent installations in your organization, or a unique account for each instance of SQL Server Agent.

When selecting an account to use for the SQL Server Agent service, your account will need the following security rights:

• In all Windows versions, permission to log on as a service (SeServiceLogonRight)
• In Windows Server, to use proxy accounts:
  • Permission to act as part of the operating system (SeTcbPrivilege) (only on Windows 2000)
  • Permission to bypass traverse checking (SeChangeNotifyPrivilege)
  • Permission to replace a process-level token (SeAssignPrimaryTokenPrivilege)
  • Permission to adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
  • Permission to log on using the batch logon type (SeBatchLogonRight)

This list is from the SQL Server Books Online at http://msdn.microsoft.com/en-us/library/ms191543.aspx. Additionally, any account you choose must be a member of the sysadmin fixed server role in the associated SQL Server instance. You will be using proxy accounts for the next level in the series, so these privileges are essential.

Changing the service account

If you are going to change the service account, it is critical that you use either the setup program or the SQL Server Configuration Manager tool to make the changes. Each of these programs will correctly grant all required permissions and security rights to enable the new service account. You should never change the service account directly with Windows. Figure 1 shows the UI to change the service account using the SQL Server Configuration Manager. Note that you will need Windows Administrator rights to use this program because you can make changes to the services themselves.

Figure 1: The proper way to change the SQL Server Agent Service Account

Security Roles for SQL Server Agent

There are three security roles available to control security in SQL Server Agent. These roles were first introduced in SQL Server 2005. These roles are:

• SQLAgentUserRole
• SQLAgentReaderRole
• SQLAgentOperatorRole

Sysadmin server role members automatically have full control over SQL Server Agent, just as they have full control of everything else in SQL Server. These roles allow non-sysadmin users to have some level of access and/or control over SQL Server agent. Each of these is a role in the msdb database, where all SQL Server Agent metadata exists in SQL Server.

An important note on how these roles work – the SQLAgentUserRole has the least amount of privileges. However, the SQLAgentReaderRole and SQLAgentOperatorRole are members of the SQLAgentUserRole, so any rights you grant to the UserRole will also automatically be gained by the other two roles. Additionally, the SQLAgentOperatorRole is a member of the SQLAgentReaderRole, so the same applies – any rights granted to the ReaderRole are automatically extended to the OperatorRole. Now you can examine each subsystem in detail.

SQLAgentUserRole

SQLAgentUserRole members have very limited permissions. They can view operators, local jobs, and job schedules that they own. They can also create jobs.

When you have membership in the SQLAgentUserRole database role in msdb, you will have the ability to view some parts of the object explorer tree for SQL Server Agent. Figure 2 is the view provided for a user with this role membership. You will be able to view only jobs you have created, as well as view and use the job activity monitor (but only for jobs you create).

Figure 2: The Object Explorer Tree as a SQLAgentUserRole member

SQLAgentReaderRole

Members of the SQLAgentReaderRole database role in msdb have all of the rights of the SQLAgentUserRole, as well as the ability to use multiserver jobs (covered in Level 12 of this Stairway). You can also view all jobs on the server, not just the jobs you own. However, all you can do with the jobs that you don’t own is view them (you can control jobs you have created).

As you can see from Figure 3, as a member of the SQLAgentReaderRole, you still see just the Jobs node, and the Job Activity Monitor, but now you can see all the jobs on the system, not just those you have created.

Figure 3: The Object Explorer Tree as a SQLAgentReaderRole member

SQLAgentOperatorRole

The SQLAgentOperatorRole database role in msdb gives a user the most privileges over SQL Server Agent (short of those provided to a sysadmin server role member). It includes all the permissions of the other two SQLAgent database roles, plus the ability to view properties for operators and proxies, and allows you to view all alerts associated with SQL Server Agent.

SQLAgentOperatorRole role members can stop, start, or run local jobs, and can clean up job history for local jobs. Role members can enable or disable a job, as well as any schedule. There is a caveat, however, that they can’t use the GUI to enable/disable a job or schedule, they must use the system stored procedure directly (sp_update_job or sp_update_schedule).

Members of the SQLAgentOperatorRole role see a graphical interface similar to Figure 4, with nearly full access to the object explorer tree for SQL Server Agent.

Figure 4: The Object Explorer Tree as a SQLAgentOperatorRole member

What’s Next

SQL Server Agent requires specific Windows and SQL Server rights for the service account used by SQL Server Agent. There are also three database roles in the msdb database that allow non-sysadmin server role member users to have access rights to SQL Server Agent, depending upon the needs of their access level. You can use these roles rather than being forced to promote someone to a sysadmin server role member to use or manage SQL Server Agent jobs, with the limitations explained in this level.

In our next level, we will start look at using Proxy accounts with SQL Server Agent to control security rights within job steps. Each job subsystem has different security considerations, and proxy accounts are a critical aspect of that security.