SQL Server Database DDL Audit

How many times as a DBA or business needs we need to Audit our database for DDL changes occured. It very much necessary to keep track of who is doing DDL Changes to your database and what are the DDL changes being done. There isnt any direct way to achieve this and if you want to go way forward you need to look out for some third party tools.

But, thanks to Microsoft, there is a tricky and accepted method of setting up the DDL Audit for your databases.

Below is the piece of code which will help you achieve in creating audting process for all of your user databases.

1. It creates a database for holding the Audit records.

2. Create a table in Audit database to hold all the DDL Statements issued against the User Database.

3. It creates a DDL trigger on all of the user database.

Note: Make sure, you provide write access to all login in SQL Server to Audit Database. This is necessary as whenever a DDL statement is issued, it writes a entry to this Audit Table.

Script:

/*Script to create Database Auditing for All Databases FOR DDL Statements*/
--CREATING DATABASE FOR SYSTEM ADMINISTRATORS RECORD
--I prefer this as we can always play around as DBA with the stuffs
--we need instead of using system databases.
USE master
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDBA_Admin_Audit')
DROP DATABASE MyDBA_Admin_Audit
GO
CREATE DATABASE MyDBA_Admin_Audit
GO
--CREATING AUDTING TABLE TO HOLD DDL STATEMENTS ISSUED AGAINST DATABASE
USE MyDBA_Admin_Audit
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DDL_Database_Audit]') AND type in (N'U'))
DROP TABLE [dbo].[DDL_Database_Audit]
GO
CREATE TABLE [dbo].[DDL_Database_Audit](
    [DDL_Audit_ID] BIGINT 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 DATABASES FOR AUDITING
--If YOU DONT WANT FOR ALL DATABASE YOU CAN HAVE THIS TRIGGER CREATED ONLY FOR DATABASE YOU NEED AUDITING
DECLARE @Database_Name VARCHAR(256)
DECLARE @SQL_Command NVARCHAR(MAX)
SET @SQL_Command = '';
--CURSOR TO CREATE TRIGGER ON EACH DATABASE
DECLARE DBCursor CURSOR FOR SELECT [NAME] FROM SYS.DATABASES WHERE [NAME] NOT IN('master','tempdb','model','msdb')
    OPEN DBCursor
    FETCH NEXT FROM DBCursor INTO @Database_Name
    WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT '---CREATING TRIGGER FOR DATABASE '+@Database_Name+'---'
            SET @SQL_Command = '
            DECLARE @CREATE_TRIGGER_SQL NVARCHAR(MAX)
            SET @CREATE_TRIGGER_SQL =
            ''CREATE TRIGGER utrg_DDL_Database_Audit
            ON DATABASE
            FOR DDL_DATABASE_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 SA_MGMT.dbo.DDL_Database_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''
            EXEC [' + @Database_Name + ']..sp_executesql @CREATE_TRIGGER_SQL'
            EXEC (@SQL_Command);
            FETCH NEXT FROM DBCursor INTO @Database_Name
        END
    CLOSE DBCursor
DEALLOCATE DBCursor
GO
--END OF CURSOR
--END OF CREATE TRIGGER
/*END of CREATE AUDIT SCRIPT*/

/*Script to Test Audit*/
USE MyDBA_Admin_Audit
Create Table TESTAudit
(
Audit_ID int
)
GO
DROP Table TESTAudit
GO
/*End of testing script*/

I will be writing in my Next Post on How to set up DDL Audit for Server Level: You can find the post here SQL Server DDL Audit Server Level