How to Identify and Fix Incorrect SQL Server Operator Settings

By:   |   Comments (2)   |   Related: > SQL Server Agent


As DBA's we rely heavily on email notifications when things go wrong in our environment such as failed jobs or alerts. In practice this is usually done through Operators in the SQL Server Agent. Recently I was working with a client and noticed a job that had been failing for as far back in time as there was history for that job. They were using the best practice of creating Operators and setting jobs to email that operator on failure. Why was I not receiving any emails, this could be business critical and job impacting. Database mail was tested and worked fine, the agent alert system was set up correctly to use the Database Mail profile.


This environment had over 50 servers going as far back as SQL 2000. I figured the best way to simplify this task was the use of the Central Management Server feature to formulate a query to retrieve information about their standard DBAGroup operator. That was the name they chose for their DBA group and yours may be different in your environment.

There are previous tips on Central Management Server as setting that up is beyond the scope of this tip.

First thing was to open the Central Management Server in SSMS.

Central Management Server

Next, right click at what level you would like to verify, in my case I wanted to verify everything so I clicked on the CMS server and selected New Query.

CMS New Query Selection

As you can see I have a new window connected to 34 out of 35 servers that I have registered in this test environment.

CMS New Query

Missing Operator

The first phase of my cleanup was to identify any servers that did not have their standard DBAGroup operator set up. Below is the query I used for this.

--Find Servers Missing DBAGroup Operator
IF NOT EXISTS(select '1' from msdb..sysoperators where name = 'DBAGroup')

After running this I had a list of servers that did not have the DBAGroup operator and it can easily be added by connecting and running sp_add_operator. That was easy enough and saved so much time, rather than having to connect to each server and browse through the SSMS GUI looking at Operators under the SQL Server Agent.

Incorrect Operator Email Address

Another thing I noticed was the email address had changed when they decided to split out SQL and Oracle DBA teams. It used to be [email protected] and they had now made it [email protected] and some of the older servers had not been changed. With that I now wanted to find servers that had the wrong email address. To do that I ran the following in my Central Management Server query window.

--Find Servers with incorrect email address
IF NOT EXISTS(select '1' from msdb..sysoperators where name = 'DBAGroup' 
          and email_address = '[email protected]')

After running this I had a nice, quick list of servers that I needed to change the email address for the Operator. This can easily be done using sp_update_operator.

Old Operators Not Being Used

As a final part of my cleanup, I saw old DBA's names as operators that have been gone for 5+ years. Some of the older servers had many operators that I noticed were not being used by jobs or alerts. With this, I wanted to identify all operators that were not set up to be notified in any job or alert, and look at deleting them as part of my cleanup process. After all, if I am already digging up and making sure everything conforms to the company standards, I may as well perform a full cleanup so everything is nice and clean. Below is the SQL used to identify operators that are not set up to be notified by any jobs or alerts. The results can easily be dropped using sp_delete_operator.

--List Operators with no alerts or jobs
select name, email_address from msdb.dbo.sysoperators
where ID not in 
     SELECT DISTINCT notify_email_operator_id from msdb..sysjobs
     SELECT DISTINCT notify_netsend_operator_id from msdb..sysjobs
     SELECT DISTINCT notify_page_operator_id from msdb..sysjobs
     SELECT DISTINCT operator_id from msdb..sysnotifications
order by name

As you can see using these simple queries through my Central Management Server I was able to get everything up to current standards by fixing incorrect email addresses, identifying servers that are missing the operator, as well as obsolete operators that are not being used. Once I was finished, my entire enterprise was clean and I started receiving the alerts I was expecting when jobs failed.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Chad Churchwell Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, September 27, 2013 - 11:15:32 AM - Adam S Back To Top (26975)

This is a great starting guide on how to find out the configurations of the servers and how to verify them going forward.

I also include version checks and server configuration checks. If they are wrong I also correct them in the this script to ensure uniformity in my servers.



Friday, March 29, 2013 - 1:28:38 AM - Gopalakrishnan Back To Top (23066)

Excellent and with Good Example.

get free sql tips
agree to terms