SQL Server DDL Trigger – Creating DDL-Trigger for ALTER_AUTHORIZATION

ddleventsql servertrigger

I need to perform some audit, when change of a securable ownership, such as

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

happens in database.

Database scope DDL-trigger seems an appropriate mechanism for this purpose. In the documentation (section DDL Statements That Have Server or Database Scope) I see that there should be ALTER_AUTHORIZATION event.

However, when I'm trying to create appropriate DDL-trigger

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

I'm getting error Msg 1084

Msg 1084, Level 15, State 1, Procedure OnAlterAuthorization, Line 2
[Batch Start Line 0] 'ALTER_AUTHORIZATION' is an invalid event type.

In sys.event_notification_event_types

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

there is no ALTER_AUTHORIZATION event, just

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

of them ALTER_AUTHORIZATION_SERVER does not suit obviously, and ALTER_AUTHORIZATION_DATABASE according to documentation

Applies to the ALTER AUTHORIZATION statement when ON DATABASE is
specified

So, the question is. Where is ALTER_AUTHORIZATION promised in the documentation? How can I catch change of a securable ownership in database?

Best Answer

I ran into the same issue you did. I would go on to discover that Event Notifications can be used to handle the AUDIT_CHANGE_DATABASE_OWNER event. (Note that this event can't be used with a DDL trigger.) I wrote an Event Notifications blog article that just happens to use the AUDIT_CHANGE_DATABASE_OWNER event as an example: SQL Server Event Handling: Event Notifications

Below is a script that can get you started.

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
Related Question