SQL Server DDL Audit Server Level

In my previous post, SQL Server Database DDL Audit I had written on how to track/audit DDL changes on Database level, which would track any DDL statement issued against the user databases and store in a Table.

Now in this Post I will post on how to track DDL Statements issued on Server. This can help you track DDL Changes done on Server level such as create database, drop database etc. Its again a Server level Trigger.

Code:

/*Script to create Server Level Auditing for All DDL Statements*/
--CREATING DATABASE FOR SYSTEM ADMINISTRATORS RECORD
CREATE DATABASE [MyDBA_Admin_Audit]
GO
--CREATING AUDTING TABLE TO HOLD DDL STATEMENTS ISSUED AGAINST DATABASE
USE [MyDBA_Admin_Audit]
GO
CREATE TABLE [dbo].[DDL_Server_Audit](
    [DDL_Audit_ID] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Event_Type] VARCHAR(100) NULL,
    [Database_Name] VARCHAR(100) NULL,
    [Schema_Name] VARCHAR(100) NULL,
    [Object_Name] VARCHAR(100) NULL,
    [Object_Type] VARCHAR(100) NULL,
    [Event_Date] DATETIME NULL,
    [Login_Name] VARCHAR (100) NULL,
    [System_User] VARCHAR(100) NULL,
    [Current_User] VARCHAR(100) NULL,
    [Original_User] VARCHAR(100) NULL,
    [Host_Name] VARCHAR (100) NULL,
    [Event_Data_Text] VARCHAR(max) NULL
) ON [PRIMARY]
GO
--CREATING TRIGGER FOR ALL SERVER FOR AUDITING
CREATE TRIGGER utrg_DDL_Server_Audit
            ON ALL SERVER
            FOR DDL_SERVER_LEVEL_EVENTS
            AS
            DECLARE @Event_Type VARCHAR(100)
            DECLARE @Schema_Name VARCHAR(100)
            DECLARE @Database_Name VARCHAR(100)
            DECLARE @Object_Name VARCHAR(100)
            DECLARE @Object_Type VARCHAR(100)
            DECLARE @Login_Name VARCHAR(100)
            DECLARE @Event_Data_Text VARCHAR(MAX)
            SELECT
            @Event_Type = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','varchar(100)')  
            ,@Database_Name = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(100)')  
            ,@Schema_Name = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(100)')  
            ,@Object_Name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)')
            ,@Object_Type = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(100)')  
            ,@Login_Name = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)')
            ,@Event_Data_Text = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
            INSERT INTO MyDBA_Admin_Audit.dbo.DDL_Server_Audit ([Event_Type], [Database_Name], [Schema_Name]
            , [Object_Name], [Object_Type], [Event_Date], [Login_Name], [System_User], [Current_User]
            , [Original_User], [Host_Name], [Event_Data_Text])
            SELECT @Event_Type, @Database_Name, @Schema_Name, @Object_Name, @Object_Type
            , getdate(), @Login_Name, SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN()
            , HOST_NAME(), @Event_Data_Text
GO
--END OF CREATE TRIGGER
/*END of CREATE AUDIT SCRIPT*/

A sample screenshot of output below.

You can find the trigger in Server Objects as this is Server Level Trigger