主页 / dba / 问题 / 6136
Accepted
db7
Asked: 2011-09-26 05:21:56 +0800 CST

当有人添加用户/登录以具有“sa”访问权限时，如何创建通知方法？

  • 772

当有人添加用户/登录以在 SQL Server 2005/2008/2008R2 上具有“sa”访问权限时，如何创建通知方法？谢谢你的帮助。

sql-server

2 个回答

  1. 您需要设置一个查看管理员列表的作业，如果该列表不符合预定义的管理员列表，则发送警报。SQL Server Denali 内置了此功能。

    • 4
  2. Best Answer

    对于 SQL 2008，服务器触发器可以执行您想要的操作，但这不是 SQL 2005 的选项。事件通知对任何一个都可以。下面的脚本在 SQL 2005 Standard 和 2008R2 Enterprise 上进行了测试。如所写，当创建登录并将其添加到 sysadmin 服务器角色时，它将在 msdb..auditRoles 表中输入一行。要为 AUDIT_ADDLOGIN_EVENT 和 AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT 获取一行，请在创建事件通知步骤中删除“WITH FAN_IN”行。

    
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;
    • 2

相关问题