Audit table changes in sql server

Suggested Videos
Part 92 - DDL Triggers in sql server
Part 93 - Server-scoped ddl triggers
Part 94 - SQL Server trigger execution order



In this video we will discuss, how to audit table changes in SQL Server using a DDL trigger.



Table to store the audit data

Create table TableChanges

(

    DatabaseName nvarchar(250),

    TableName nvarchar(250),

    EventType nvarchar(250),

    LoginName nvarchar(250),

    SQLCommand nvarchar(2500),

    AuditDateTime datetime

)

Go

The following trigger audits all table changes in all databases on a SQL Server

CREATE TRIGGER tr_AuditTableChanges

ON ALL SERVER

FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE

AS

BEGIN

    DECLARE @EventData XML

    SELECT @EventData = EVENTDATA()

    INSERT INTO SampleDB.dbo.TableChanges

    (DatabaseName, TableName, EventType, LoginName,

     SQLCommand, AuditDateTime)

    VALUES

    (

         @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(250)'),

         @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)'),

         @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(250)'),

         @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(250)'),

         @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2500)'),

         GetDate()

    )

END

In the above example we are using EventData() function which returns event data in XML format. The following XML is returned by the EventData() function when I created a table with name = MyTable in SampleDB database.

<EVENT_INSTANCE>

  <EventType>CREATE_TABLE</EventType>

  <PostTime>2015-09-11T16:12:49.417</PostTime>

  <SPID>58</SPID>

  <ServerName>VENKAT-PC</ServerName>

  <LoginName>VENKAT-PC\Tan</LoginName>

  <UserName>dbo</UserName>

  <DatabaseName>SampleDB</DatabaseName>

  <SchemaName>dbo</SchemaName>

  <ObjectName>MyTable</ObjectName>

  <ObjectType>TABLE</ObjectType>

  <TSQLCommand>

    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"

                ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"

                ENCRYPTED="FALSE" />

    <CommandText>

      Create Table MyTable

      (

         Id int,

         Name nvarchar(50),

         Gender nvarchar(50)

      )

    </CommandText>

  </TSQLCommand>

</EVENT_INSTANCE>