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?
sp_trace_setevent
153