SQL Server Agent: Difference between revisions

Revision as of 18:34, 23 June 2017

Revision as of 13:35, 24 April 2018

Line 5:

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.

<ref>{{Cite web|url=https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent|title=SQL Server Agent|last=stevestein|language=en-us|access-date=2018-04-24}}</ref>

== 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|windows user]] account that does have access to the resources needed by the job.

== SQL Server Agent Subsystems ==

SQL Server Agent uses various subsystems that help with its tasks, they also allow more complex security models than SQL Agent Proxies.

SQL Server Agent has twelve subsystems:

* [[Active Scripting|ActiveX Script]]

* [[Operating system|Operation System]]

* [[PowerShell]]

* [[Replication Distributor]]

* [[Replication Merge]]

* [[Replication Queue Reader]]

* [[Replication Snapshot]]

* [[Replication Transaction Log Reader]]

* [[Analysis Services Command]]

* [[Analysis Services Query]]

* [[SSIS Package Execution]]

* [[Transact-SQL|Transact SQL]]

<ref>{{Cite book|url=https://www.worldcat.org/oclc/889954866|title=Professional Microsoft SQL Server 2014 administration|last=Adam,|first=Jorgensen,|others=Ball, Bradley,, Wort, Steven,, LoForte, Ross,, Knight, Brian, 1976-|isbn=9781118859261|___location=Hoboken|oclc=889954866}}</ref>

==References==