SQL Server Agent

SQL Server Agent

• Runs as a Windows service
• Must be running to execute jobs, fire alerts, contact operators
• Start mode should be set to Automatic
• You should automate its restart on the server:
  Start > Admin Tools > Computer Management > Services and Applications > Services > SQL Server Agent (dbl-click) > Recovery > change failure actions to Restart the Service
• You can also restart it in the SQL Server Agent Properties.

SQL Server Agent Security

Jobs need to be able to access many types of objects in SQL Server, as well as operating system files and folder. The account that is used to start-up the service defines its security level. The Local Service and Network Service accounts can be used though neither is recommended. A Windows domain account should be used, with only the required permissions.

By default only members of the sysadmin fixed server role can administer SQL Server Agent. There are fixed server roles for working specifically with SQL Server Agent:

• SQLAgentUserRole - Permissions only on local jobs and job schedules they own
• SQLAgentReaderRole - Can view list of multi-server jobs, properties, histories, schedules.
• SQLAgentOperatorRole - Can execute, stop, start all local jobs. Can delete job history for local jobs.

When users who are not members of one of these roles are connected to SQL Server, SQL Server Agent node in Object Explorer is not visible.