DDL Triggers To Audit Server Level Events - SQL Server

In my earlier post I explained how to log DDL events, now we will see another example how to audit or log server level events.

CREATE TABLE DDL_ServerAudit_Log

(

       EventTime            DATETIME,

       LoginName            VARCHAR(250),
       UserName             VARCHAR(250),
       DatabaseName         VARCHAR(250),
       SchemaName           VARCHAR(250),
       ObjectName           VARCHAR(250),
       ObjectType           VARCHAR(250),
       DDLCommand           VARCHAR(MAX)
)

 

CREATE TRIGGER trg_AuditServerEvents ON ALL SERVER FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN, GRANT_SERVER  AS

DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO kalyandb..DDL_ServerAudit_Log VALUES
(       REPLACE(CONVERT(VARCHAR(50),@data.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/LoginName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/UserName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/DatabaseName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/SchemaName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectType)')),
        CONVERT(VARCHAR(MAX),@data.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
)

To Verify 

USE master

CREATE LOGIN test WITH PASSWORD='Admin@123'
SELECT * FROM kalyandb..ddl_serveraudit_log


 Disable Trigger

DISABLE TRIGGER ALL ON DATABASE
GO
DISABLE TRIGGER ALL ON ALL SERVER

View triggers
select name from sys.triggers

select definition from sys.sql_modules where object_id = select object_id from sys.triggers where name = 'trigger_name')

Deleting triggers
drop trigger trigger_name on database