Share via

Automating SQL Server Administration with SQL Server Agent

  • Article

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks called jobs. Each job contains one or more job steps. Each step contains its own task - for example, backing up a database.

SQL Server Agent uses SQL Server to store job information. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For example, if you want to back up all the company servers every weekday at 10-pm, you can automate the task as a SQL Server Agent job. Schedule the backup to run after 22:00 Monday through Friday; if the backup encounters a problem, SQL Server Agent can record the event and notify you.

Note  By default, the SQL Server Agent service is disabled when SQL Server 2005 or later is installed unless the user explicitly chooses to autostart the service.

To automate administration, follow these steps:

  1. Establish which administrative tasks or server events occur regularly and whether these tasks or events can be administered programmatically. A task is a good candidate for automation if it involves a predictable sequence of steps and occurs at a specific time or in response to a specific event.
  2. Define a set of jobs, schedules, alerts, and operators by using SQL Server Management Studio, Transact-SQL scripts, or SQL Server Management Objects (SMO). For more information, see Creating Jobs in SQL Server Books Online.
  3. Run the SQL Server Agent jobs you have defined.

Note

For the default instance of SQL Server, the SQL Server service is named SQLSERVERAGENT. For named instances, the SQL Server Agent service is named SQLAgent$instancename.

If you run multiple instances of SQL Server, you can use multiserver administration to automate tasks common across all instances. For more information, see Automating Administration Across an Enterprise.

Fixed database roles and least privileges

SQL Server Agent has three roles that reside in the MSDB system database. You can use these roles to fine tune privileges for SQL Server Agent jobs. Job management is an area where recent security improvements are a welcome addition compared to previous versions of SQL Server.

 

ID

Role

Description

1

SQLAgentUserRole
  • Manage Jobs that they own.

2

SQLAgentReaderRole
  • All of the SQLAgentUserRole rights.
  • Ability to review multiserver jobs, their configurations, and history.

3

SQLAgentOperatorRole
  • All of the SQLAgentReaderRole rights.
  • The ability to review operators, proxies, and alerts.
  • Execute, stop or start all local jobs.
  • Delete the job history for any local job.
  • Enable or disable all local jobs and schedules.

 

Although production DBA’s are likely to have System Administrator rights, SQL Server Agent Fixed Database Roles should be considered for:

  • Junior DBA’s
  • Developers
  • Data Warehouse Developers
  • IT Operations
  • Management

These rights are granted at the MSDB database level. To grant rights, use the following steps:

  1. Open SQL Server Management Studio.
  2. Navigate to Databases | System Databases | MSDB | Security | Roles | Database Roles
  3. Double-click one of the roles to add members to it.
  4. Use the Database Role Properties window to manage users.

Best practices

  • Take advantage of granular security privileges to secure your SQL Server Agent jobs using least privileges.
  • Carefully evaluate the privileges requested by users and IT personnel. Once you have a clear understanding of their needs, grant the appropriate privileges without compromising the security of your system.

Additional information in SQL Server Books Online: