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