DDL-trigger for ALTER_AUTHORIZATION

Question

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?

Answer 1

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://learn.microsoft.com/en-us/sql/relational-databases/tutorial-signing-stored-procedures-with-a-certificate
            EXEC msdb.dbo.sp_send_dbmail 
                @recipients = '[email protected]', 
                @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

Answer 2

I found that in spite of that event ALTER_AUTHORIZATION_DATABASE according to documentation is declared as

Applies to the ALTER AUTHORIZATION statement when ON DATABASE is specified

it fires not only when database owner changes, but also when owner of a securable changes in database.

In other words, DDL-trigger

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

works not only for

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

But e.g. for

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

or

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

as well.