{"id":1085,"date":"2011-10-06T17:26:52","date_gmt":"2011-10-06T17:26:52","guid":{"rendered":"http:\/\/craftydba.com\/?p=1085"},"modified":"2017-10-15T16:31:22","modified_gmt":"2017-10-15T16:31:22","slug":"defining-operators","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1085","title":{"rendered":"Defining Operators"},"content":{"rendered":"

SQL Server supports the notification of administrators through operators<\/a>. Operators are aliases for users or groups that can receive notifications when jobs complete or alerts have been raised. Each SQL Server instance can have its own set of defined operators.<\/p>\n

Today, I am going to walk you thru the following tasks in Transaction SQL.<\/p>\n

    \n
  1. Adding an operator<\/li>\n
  2. Updating an operator<\/li>\n
  3. Deleting an operator<\/li>\n
  4. Defining a fail-safe operator<\/li>\n<\/ol>\n

    To define an operator, one needs to know the email address of the user and what time he is responsible for the SQL Server in question. The start and end times are expressed using a twenty four hour clock. Days of the week are expressed by summing individual days expressed as powers of two.<\/p>\n

    1 = Sunday
    \n2 = Monday
    \n4 = Tuesday
    \n8 = Wednesday
    \n16 = Thursday
    \n32 = Friday
    \n64 = Saturday<\/p>\n

    The example below adds me as an operator. It is important to note that a pager address was supplied. With today\u2019s mail system and hand held devices; it is easy to find out the SMTP text address of the phone given the provider.<\/p>\n

    \r\n-- Add operator with email\/pager from 8am-to-5pm weekdays\r\nEXEC msdb.dbo.sp_add_operator\r\n    @name = N'John Miner',\r\n    @enabled = 1,\r\n    @email_address = N'john@craftydba.com',\r\n    @pager_address = N'14015551212@mobile.att.net',\r\n    @weekday_pager_start_time = 080000,\r\n    @weekday_pager_end_time = 170000,\r\n    @pager_days = 62 ;\r\nGO\r\n\r\n-- Show the new entry\r\nEXEC msdb.dbo.sp_help_operator\r\n    @operator_name = N'John Miner' ;\r\nGO\r\n<\/pre>\n<\/p>\n
    To view the settings of an operator, use the sp_help_operator stored procedure.<\/p>\n
    Most things in life are never perfect on the first try. Therefore, TSQL supplies an update stored procedure to modify an existing operator. The example before disables the operator we just defined.<\/p>\n
    \r\n-- Disable the operator\r\nEXEC msdb.dbo.sp_update_operator \r\n    @name = N'John Miner',\r\n    @enabled = 0;\r\n\r\n-- Show the updated entry\r\nEXEC msdb.dbo.sp_help_operator\r\n    @operator_name = N'John Miner' ;\r\nGO\r\n<\/pre>\n<\/p>\n
    Last but not least, people and groups change over time. It will be neccessary to remove an operator when it is no longer needed. See sample code below.<\/p>\n
    \r\n-- Remove the operator\r\nEXEC msdb.dbo.sp_delete_operator @name = 'John Miner' ;\r\nGO\r\n\r\n-- Show the deleted entry\r\nEXEC msdb.dbo.sp_help_operator\r\n    @operator_name = N'John Miner' ;\r\nGO\r\n<\/pre>\n<\/p>\n
    I suggest that you define operators using AD groups and allow the Active Directory administrator deal with the maintenance overhead. Also, use a 24 x 7 notification in which the person on call reacts to the notifications and all other DBA’s will be notified but are not responsible at that time.<\/p>\n
    Many of these server level services are defined with tables in the msdb database. Operators are not exception to this fact since the msdb.dbo.sysoperators table contains information<\/p>\n
    The fail-safe operator<\/a>(s) are responsible if the alert could not be paged or SQL Server Agent cannot access system tables in the msdb database. The operator can be setup thru the SQL Server Management Studio (SSMS) under SQL Server Agent Properties, Alert system, Enable fail-safe operator. Truth be told, the information for the fail safe operator is stored in the operating system registry.<\/p>\n
    The following stored procedure in the master database can be called to define the fail-safe operator.<\/p>\n
    \r\n-- Define a fail-safe operator\r\nEXEC master.dbo.sp_MSsetalertinfo @failsafeoperator=N'John Miner';\r\n<\/pre>\n<\/p>\n
    The key points to remember from this article are operators are defined to recieve notifications from alerts and jobs.  TSQL supplies system stored procedures to add, modify and delete these operators. Always define a fail-safe operator just in-case the msdb database is having issues.<\/p>\n
    I will continue next time by talking about setting up system alerting and notifications.<\/p>\n","protected":false},"excerpt":{"rendered":"
    SQL Server supports the notification of administrators through operators. Operators are aliases for users or groups that can receive notifications when jobs complete or alerts have been raised. Each SQL Server instance can have its own set of defined operators. Today, I am going to walk you thru the following tasks in Transaction SQL. Adding an operator Updating an operator Deleting an operator Defining a fail-safe operator To define an operator, one needs to know the email address of the user and what time he is responsible for the SQL…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[117,164,12,15,160,162,159,163,161,28,29],"class_list":["post-1085","post","type-post","status-publish","format-standard","hentry","category-db-admin","tag-database-administrator","tag-defining-operators","tag-free-code","tag-john-f-miner-iii","tag-sp_add_operator","tag-sp_delete_operator","tag-sp_help_operator","tag-sp_mssetalertinfo","tag-sp_update_operator","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1085","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1085"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1085\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1085"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1085"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1085"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}