Configure Default Operator in SQL Server for sending alerts via Database Email

Ever wondered how to create a Default Operator in SQL Server for Sending Alerts configured in your Jobs using Database Emails? Well you can definately do it using SQL Server Management Studio, but what if you have 100 new servers installed and want to create operators on all servers. Lengthy process right? Not actually.

You can configure operator on multiple servers using CMS and below is the script which you need to run on a CMS.

An over view of CMS(Central Management Server) can be found here

How to configure CMS(Central Management Server) can be found here

Here is the script which can be used

USE msdb
GO

EXEC dbo.sp_add_operator
   @name = 'MyOperatorName',
   @enabled = 1,
   @email_address = EmailIDtoBeSentFrom@myEmailID.com

GO

USE master
GO

EXEC master.dbo.xp_instance_regwrite
  N'HKEY_LOCAL_MACHINE',
  N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
  N'AlertFailSafeOperator',
  REG_SZ,
  'MyOperatorName'
GO