SQL Server Agent

SQL Server Agent is a component of Microsoft SQL Server which schedules jobs and handles other automated tasks.^[1] It runs as a Windows service so it can start automatically when the system boots or it can be started manually.

Typical system tasks performed include scheduling maintenance plans (such as backups), handling Reporting Services subscriptions and performing log shipping sub-tasks (backup, copy, restore & check). User tasks, such as scheduling some T-SQL or command line statement are also common.

SQLAgent has support for operators and alerts, so that administrators can be notified, e.g. by email.

Jobs

A job is a specified series of actions that SQL Server Agent performs. Use jobs to define an administrative task that can be run one or more times and monitored for success or failure. A job can be run from one local server or on multiple remote servers.

You can run jobs in the following ways:

Through one or more schedules

In response to one or more alerts

By executing the specific stored procedure

Each action within a job is a job step. Each job step runs in a specific security context. These jobs can be handled by SQL Server Agent users.

^[2]

Agent Roles

There are roles that are required for a user to have before they are able to use SQL Server Agent.

They are as follows:

SQL Agent User Role

Members of the user role have the most restricted access to SQL Server Agent. They can see only the Jobs node under SQL Server Agent, and can access only local jobs and schedules that they own. They cannot use multi-server 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.

SQL Agent Reader Role

The reader role includes all the permissions of thee user role. Members of this role can create and run the same things as a user, but can see the list of multi-server jobs, including their properties and history. They can also see all the jobs and schedules on the local server, not just the ones they own.

SQL Agent Operator Role

The operator role is the least restricted role and includes all the permissions of the reader role and the user role. This role has additional read capabilities and 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. They can delete history for any job.

SQL Server Agent Proxies

SQL Server Agent Proxies help define the security under which different jobs run. In a case where the user creates a job that does not have permission to access the resources needed by the job, the user can specify a proxy. The proxy will contain the credentials of a windows user account that does have access to the resources needed by the job.