Assign Alerts to an Operator

This topic describes how to assign Microsoft SQL Server Agent alerts to operators so they can receive notifications about jobs in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Security

• To assign alerts to an operator, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

• SQL Server Management Studio provides an easy, graphical way to manage the entire alerting system. Using Management Studio is the recommended way to configure your alert infrastructure.

• To send a notification in response to an alert, you must first configure SQL Server Agent to send mail. For more information, see Configure SQL Server Agent Mail to Use Database Mail.

• If a failure occurs when sending an e-mail message or pager notification, the failure is reported in the SQL Server Agent service error log.

Security

Permissions

Only members of the sysadmin fixed server role can assign alerts to operators.

[Top]

Using SQL Server Management Studio

To assign alerts to an operator

1. In Object Explorer, click the plus sign to expand the server that contains the operator to which you want to assign an alert.

2. Click the plus sign to expand SQL Server Agent.

3. Click the plus sign to expand the Operators folder.

4. Right-click the operator to which you want to assign an alert and select Properties, and select the Notifications page.

5. In the operator_name Properties dialog box, under Select a page, select Notifications.

6. Under View notifications sent to this user by, select Alerts to view a list of alerts sent to this operator or select Jobs to view a list of jobs that send notifications to this operator. Select one or more of the following checkboxes to define the notification method for each notification as necessary: E-mail, Pager, or Net send.

7. When finished, click OK.

[Top]

Using Transact-SQL

To assign alerts to an operator

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

   -- adds an e-mail notification for the specified alert (Test Alert)
   -- This example assumes that Test Alert already exists and that François Ajenstat is a valid operator name.
   USE msdb ;
   GO
   
   EXEC dbo.sp_add_notification
    @alert_name = N'Test Alert',
    @operator_name = N'François Ajenstat',
    @notification_method = 1 ;
   GO
   

For more information, see sp_add_notification (Transact-SQL).

[Top]