ALTER_AUTHORIZATION 的 DDL 觸發器

更改安全所有權時，我需要執行一些審核，例如

ALTER AUTHORIZATION ON SCHEMA::[SchemaName] TO [PrincipalName];

發生在數據庫中。

數據庫範圍 DDL-trigger 似乎是用於此目的的適當機制。在文件（具有伺服器或數據庫範圍的 DDL 語句部分）中，我看到應該有ALTER_AUTHORIZATION事件。

但是，當我嘗試創建適當的 DDL 觸發器時

CREATE TRIGGER [OnAlterAuthorization] ON DATABASE
FOR ALTER_AUTHORIZATION
AS
BEGIN
   PRINT 'Perform audit';
END

我收到錯誤消息 1084

消息 1084，級別 15，狀態 1，過程 OnAlterAuthorization，第 2 行

$$ Batch Start Line 0 $$“ALTER_AUTHORIZATION”是無效的事件類型。

在sys.event_notification_event_types

SELECT type_name
FROM sys.event_notification_event_types
WHERE type_name LIKE 'ALTER_AUTHOR%';

沒有ALTER_AUTHORIZATION事件，只是

type_name
-----------------------------
ALTER_AUTHORIZATION_SERVER
ALTER_AUTHORIZATION_DATABASE

其中ALTER_AUTHORIZATION_SERVER不適合顯然，並且ALTER_AUTHORIZATION_DATABASE根據文件

指定 ON DATABASE 時適用於 ALTER AUTHORIZATION 語句

所以，問題是。ALTER_AUTHORIZATION文件中的承諾在哪裡？如何捕捉數據庫中安全所有權的變化？

我發現儘管ALTER_AUTHORIZATION_DATABASE根據文件發生了該事件，但仍被聲明為

指定 ON DATABASE 時適用於 ALTER AUTHORIZATION 語句

它不僅在數據庫所有者更改時觸發，而且在數據庫中安全更改的所有者時觸發。

換句話說，DDL 觸發器

CREATE TRIGGER [OnAlterAuthorization] ON DATABASE
FOR ALTER_AUTHORIZATION_DATABASE
AS
BEGIN
   PRINT 'Check ownership';
END

不僅適用於

ALTER AUTHORIZATION ON DATABASE::[DbName] TO [PrincipalName];

但是例如對於

ALTER AUTHORIZATION ON SCHEMA::[SchemaName] TO [PrincipalName];

要麼

ALTER AUTHORIZATION ON ROLE::[RoleName] TO [PrincipalName];

也是。

我遇到了和你一樣的問題。我會繼續發現事件通知可用於處理AUDIT_CHANGE_DATABASE_OWNER事件。（注意，這個事件不能和 DDL 觸發器一起使用。）我寫了一篇 Event Notifications 部落格文章，恰好以AUDIT_CHANGE_DATABASE_OWNER事件為例：SQL Server 事件處理：事件通知

下面是一個可以幫助您入門的腳本。

USE SomeDatabase
GO

--Create a queue just for change db owner events.
CREATE QUEUE queChangeDBOwnerNotification

--Create a service just for change db owner events.
CREATE SERVICE svcChangeDBOwnerNotification
ON QUEUE queChangeDBOwnerNotification ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

-- Create the event notification for change db owner events on the service.
CREATE EVENT NOTIFICATION enChangeDBOwner
ON SERVER
WITH FAN_IN
FOR AUDIT_CHANGE_DATABASE_OWNER
TO SERVICE 'svcChangeDBOwnerNotification', 'current database';
GO

CREATE PROCEDURE dbo.ReceiveChangeDBOwner
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @MsgBody XML

   WHILE (1 = 1)
   BEGIN
       BEGIN TRANSACTION

       -- Receive the next available message FROM the queue
       WAITFOR (
           RECEIVE TOP(1) -- just handle one message at a time
               @MsgBody = CAST(message_body AS XML)
               FROM queChangeDBOwnerNotification
       ), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE and go away
       -- If we didn't get anything, bail out
       IF (@@ROWCOUNT = 0)
       BEGIN
           ROLLBACK TRANSACTION
           BREAK
       END 
       ELSE
       BEGIN
           --Interrogate the event data for relevant properties/values.
           DECLARE @Cmd VARCHAR(1024)
           DECLARE @MailBody NVARCHAR(MAX)
           DECLARE @Subject NVARCHAR(255)

           SET @Cmd = @MsgBody.value('(/EVENT_INSTANCE/TextData)[1]', 'VARCHAR(1024)')
           SET @Subject = @@SERVERNAME + ' -- ' + @MsgBody.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(128)' )    

           --Build an html table for use with an html-formatted email message.
           SET @MailBody = 
               '<table border="1">' +
               '<tr><td>Server Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
               '<tr><td>Start Time</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/StartTime)[1]', 'VARCHAR(128)' ) + '</td></tr>' +  
               '<tr><td>Session Login Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
               '<tr><td>Login Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)') + '</td></tr>' + 
               '<tr><td>Windows Domain\User Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'VARCHAR(256)') + '\' +
                   @MsgBody.value('(/EVENT_INSTANCE/NTUserName)[1]', 'VARCHAR(256)') + '</td></tr>' +  
               '<tr><td>DB User Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/DBUserName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
               '<tr><td>Host Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/HostName)[1]', 'VARCHAR(128)' ) + '</td></tr>' +  
               '<tr><td>Application Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
               '<tr><td>Command Succeeded</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/Success)[1]', 'VARCHAR(8)' ) + '</td></tr>' + 
               '</table><br/>' +
               '<p><b>Text Data:</b><br/>' + REPLACE(@Cmd, CHAR(13) + CHAR(10), '<br/>') +'</p><br/>'
           --PRINT @Subject
           --PRINT @MailBody

           --Note: you may need to set [msdb] to trustworthy for this to work.
           --Another option is to sign a stored proc with a certificate.
           --See: https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-signing-stored-procedures-with-a-certificate
           EXEC msdb.dbo.sp_send_dbmail 
               @recipients = 'You@YourDomain.com', 
               @subject = @Subject,
               @body = @MailBody,
               @body_format = 'HTML',
               @exclude_query_output = 1
           /*
               Commit the transaction.  At any point before this, we 
               could roll back -- the received message would be back 
               on the queue AND the response wouldn't be sent.
           */
           COMMIT TRANSACTION
       END
   END
END
GO

ALTER QUEUE dbo.queChangeDBOwnerNotification 
WITH 
   STATUS = ON, 
   ACTIVATION ( 
       PROCEDURE_NAME = dbo.ReceiveChangeDBOwner, 
       STATUS = ON, 
       MAX_QUEUE_READERS = 1, 
       EXECUTE AS OWNER) 
GO

引用自：https://dba.stackexchange.com/questions/168102