Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I create a notification method when someone adds a user/login to have ‘sa’ access on SQL Server 2005/2008/2008R2? Thanks for your help.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

For SQL 2008 a Server trigger would do what you want but that's not an option on SQL 2005. Event Notification will do for either. The script below was tested on SQL 2005 Standard and 2008R2 Enterprise. As written it will enter a row in the msdb..auditRoles table when a login is created and added to the sysadmin server role. To get a row for both the AUDIT_ADDLOGIN_EVENT and the AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT remove the "WITH FAN_IN" line in the create event notification step.


USE msdb

-- The table storing Event data
CREATE TABLE auditRoles (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
NTUserName VARCHAR(100),
Success INT,
FullLog XML
)

--  The Queue
CREATE QUEUE auditRolesQueue WITH STATUS = OFF;

--  The service
CREATE SERVICE auditRolesService
ON QUEUE auditRolesQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

--  The route
CREATE ROUTE auditRolesRoute
WITH SERVICE_NAME = 'auditRolesService',
ADDRESS = 'LOCAL'

--  Event Notification
--  Remove the WITH FAN_IN to get multiple events for the same statement
CREATE EVENT NOTIFICATION auditRoles_Event_Notification
ON SERVER 
WITH FAN_IN 
FOR AUDIT_ADDLOGIN_EVENT, AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT
TO SERVICE 'auditRolesService', 'current database'

--  The proc to handle the events
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE auditRolesProc
AS
 BEGIN
   SET NOCOUNT ON;
   DECLARE @message_body XML,
           @message_type_name NVARCHAR(256),
           @dialog UNIQUEIDENTIFIER ;

   --Endless loop
   WHILE (1 = 1)
    BEGIN
      BEGIN TRANSACTION ;

    -- Receive the next available message
      WAITFOR (
         RECEIVE TOP(1) 
            @message_type_name=message_type_name,
            @message_body=message_body,
            @dialog = conversation_handle
         FROM auditRolesQueue
      ), TIMEOUT 2000

      --Rollback and exit if no messages were found
      IF (@@ROWCOUNT = 0)
       BEGIN
         ROLLBACK TRANSACTION 
         BREAK 
      END ;

      --End conversation of end dialog message
      IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
       BEGIN
         PRINT 'End Dialog received for dialog # ' + CAST(@dialog As NVARCHAR(40)) ;
         END CONVERSATION @dialog ;
      END ;
      ELSE
       BEGIN
      INSERT INTO msdb.dbo.auditRoles (
         EventTime,
         EventType,
         LoginName,
         HostName,
         NTUserName,
         Success,
         FullLog
          )
      VALUES (
         CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
         CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
         CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
         CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS VARCHAR(100)),
         CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS VARCHAR(100)),
         CAST(CAST(@message_body.query('/EVENT_INSTANCE/Success/text()') AS VARCHAR(64)) AS INTEGER),
         @message_body
          )
      END
   COMMIT TRANSACTION
   END
END

--  Alter and enable the Queue
ALTER QUEUE auditRolesQueue
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = auditRolesProc ,
MAX_QUEUE_READERS = 2, EXECUTE AS SELF ),
STATUS = ON;
share|improve this answer

You would need to setup a job which looks at the list of admins and sends an alert if the list doesn't meet the pre-defined list of admins. SQL Server Denali has this functionality built in.

share|improve this answer
    
Thank you, Do you have any sample script? –  db7 Sep 26 '11 at 10:28
    
No I don't have a sample script handy. You'll want to look at sys.server_principals and sys.server_role_members for the current role membership. –  mrdenny Sep 26 '11 at 21:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.