Create SQL Server Operators
How to Create SQL Server Operators
SQL Server operators are aliases for people or groups that can receive notifications about job statuses, alerts, or system events via email, pager, or net send. Setting up operators is a crucial part of SQL Server Agent configuration to ensure that the right teams are alerted in case of job failures or other critical issues.
When to Use Operators
Operators are typically used in conjunction with:
-
SQL Server Agent Jobs (e.g., job failure notifications)
-
SQL Alerts (e.g., severity level 17 errors or custom alerts)
-
Database Mail for sending email notifications
Steps to Create Operators
Below are example scripts to create multiple operators in the msdb database. Each operator includes:
-
A name (used to identify the operator)
-
An email address for notifications
-
A schedule specifying when the operator is available (in this case, 24×7)
Note: Replace
tomailid@yourdomain.***with your actual support email addresses.
Script:-
GO
EXEC msdb.dbo.sp_add_operator @name=N’SQLDBATeam’,
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235900,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235900,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235500,
@pager_days=0,
@email_address=N’tomailid@yourdomain.***’,
@category_name=N'[Uncategorized]’
GOEXEC msdb.dbo.sp_add_operator @name=N’SupportTeam1′,
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235900,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235900,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235500,
@pager_days=0,
@email_address=N’tomailid@yourdomain.***’,
@category_name=N'[Uncategorized]’
GO
EXEC msdb.dbo.sp_add_operator @name=N’AppSupport2′,
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235900,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235900,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235500,
@pager_days=0,
@email_address=N’tomailid@yourdomain.***’,
@category_name=N'[Uncategorized]’
GO
EXEC msdb.dbo.sp_add_operator @name=N’SUPPORT1′,
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235900,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235900,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235500,
@pager_days=0,
@email_address=N’tomailid@yourdomain.***’,
@category_name=N'[Uncategorized]’
GO
EXEC msdb.dbo.sp_add_operator @name=N’SUPPORT2′,
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235900,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235900,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235500,
@pager_days=0,
@email_address=N’tomailid@yourdomain.***’,
@category_name=N'[Uncategorized]’
GO
EXEC msdb.dbo.sp_add_operator @name=N’SUPPORT3′,
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235900,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235900,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235500,
@pager_days=0,
@email_address=N’tomailid@yourdomain.***’,
@category_name=N'[Uncategorized]’
GO
PRINT ‘Operators Done …………………’
GO
Architecture Diagram of SQL Operator
Best Practices
- Always use valid and monitored email addresses.
-
Keep operator names meaningful (e.g., include team or role info).
-
Periodically review and update operator contact info.
-
Use categories to organize operators when needed.
