Consolidate Multiple Email Operators

Suppose you’ve been working on cleaning up SQL Agent jobs and their email operators and find that you have more than one operator with the same email address. How would you tidy this up to ease administration?

Let’s do a little setup to demonstrate the scenario.  The T-SQL below will create three operators with different names but the same email address.  Then it updates two of my existing test jobs to use those email operators when the job fails.  I am also logging failures to the Windows Event Log.

USE [msdb];
GO

EXEC msdb.dbo.sp_add_operator @name=N'DBA', 
 @enabled=1, 
 @pager_days=0, 
 @email_address=N'[email protected]'
GO

EXEC msdb.dbo.sp_add_operator @name=N'Super Human Person', 
 @enabled=1, 
 @pager_days=0, 
 @email_address=N'[email protected]'
GO

EXEC msdb.dbo.sp_add_operator @name=N'SQL Server Team', 
 @enabled=1, 
 @pager_days=0, 
 @email_address=N'[email protected]'

Now I will run this simple query to find out what my email operators and email addresses are.

USE msdb;
GO

SELECT id
, name
, email_address
FROM sysoperators

Here is the result set.

How does this sort of thing happen, you ask.  Well, it doesn’t just happen in contrived blog posts!  Suppose there is a fair amount of turnover in a DBA environment and the DBA’s are not always checking what the last person did.  Each one after another goes in and does her own thing.  Voila!  You end up with duplicate email addresses for email operators.

Some jobs have “DBA” as the email operator.  Some have “Super Human Person” as the email operator.  You might even have a couple of “test” operators in their with “[email protected]”.

Maybe the DBA didn’t think about the fact that he might not always be the DBA and so it might be better to set up an email distribution group that was controlled by the Exchange person.  That would mean that as DBA’s came and went the same email operators could be used in SQL Server. Who knows! But I have seen this.  So what now?

sp_delete_operator to the rescue!

Here is our example.

EXEC sp_delete_operator @name = 'DBA', @reassign_to_operator = 'SQL Server Team';
GO
EXEC sp_delete_operator @name = 'Super Human Person', @reassign_to_operator = 'SQL Server Team';

So what this does is it deletes the email operator in the @name parameter and assigns all jobs that had the @name operator to the operator assigned to the @reassign_to_operator parameter.  Now when you run the second T-SQL snippet from above against the sysoperators table in msdb, you get back just the operator name “SQL Server Team.”