Configuring SQL Server Agent Operators
Creating operators within SQL Server Agent is a relatively simple task but by default no operators are set up within an instance of SQL Server. In order be able to send out e-mail notifications within SQL Server Agent Jobs you will need to have operators configured so that they can be selected as recipients within the job’s Notification page.
There are two ways to create an operator. You can do it from within the SQL Server Agent drop-down while connected to the SQL server or you can do it using a SQL script. I will cover both methods below.
The easiest way to create an operator is to simply use SQL Management Studio’s graphic interface. To do this, expand the SQL Server Agent object on the server, right-click Operators and select New Operator…
Once selected the following New Operator window will be displayed. To keep things simple, all you really need to do here is provide a Name and E-mail Name (e-mail address). You can configure an on-duty schedule if you would like but in most cases this is not necessary. Additionally, you can use the Notifications page to view all notifications sent to the user once they have been set up and added to Agent jobs.
Once you add the Name and E-mail name and click OK the operator will be added to the Operator folder within the SQL Server Agent drop-down on the server.
The other method for creating an operator is to do it using a SQL script. The following script will create the same operator as the steps above. You can modify the script by replacing the @name
and @email_address
values in order to create a new operator.
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name='Jack Worthen',
@enabled=1,
@pager_days=0,
@email_address='jworthen@jackworthen.com'
GO
You can also assign Active Directory groups as operators should you need to notify multiple individuals when a SQL Agent Job fails or succeeds. Since the Notification options within the SQL Server Agent only allows a single operator to be selected this is good practice as you can have multiple individuals within the AD group so you can ensure the notification goes out to everyone who may need to be aware.