Archive

Posts Tagged ‘sql trigger’

SQL Server – Auditing Schema Changes using DDL Triggers

15.07.2011 8 comments

Yesterday, I posted about SQL Server – Management Studio – Schema Changes History Report and Default Trace and their limitations. It does not capture the details of modification. i.e. When a new column is added to a table, you can find out when the changes to the Table were made using Default Trace, but it does not tell which column was added, or what command was used to add.

This information can be captured using DDL Triggers, it is a special kind of trigger which fires in response to DDL statements. A list of events that can be used to fire a DDL Trigger is available on BOL @ DDL Events for Use with DDL Triggers.

DDL Triggers can be of two scopes.

1. Server Level Trigger – By creating Trigger on Server, you can capture events for all Databases on the Server,

2. Database Level Trigger – This scope is limited to a single Database on which the Trigger is created.

 

DDL Trigger can capture the information about the EVENT that fired it by using EVENTDATA() function. EVENTDATA() returns an XML. For example below XML is returned when a DDL statement for CREATE TABLE is executed:

— © 2011 – Vishal (http://SqlAndMe.com)

 

USE SqlAndMe

 

CREATE TABLE dbo.SalesYTD

(

CalendarYear  VARCHAR(7),

SalesYTD      DECIMAL(10,2)

)

EVENTDATA() XML:

<EVENT_INSTANCE>

       <EventType>CREATE_TABLE</EventType>

       <PostTime>2011-07-15T20:10:10.733</PostTime>

       <SPID>51</SPID>

       <ServerName>VGAJJAR</ServerName>

       <LoginName>sa</LoginName>

       <UserName>dbo</UserName>

       <DatabaseName>SqlAndMe</DatabaseName>

       <SchemaName>dbo</SchemaName>

       <ObjectName>SalesYTD</ObjectName>

       <ObjectType>TABLE</ObjectType>

       <TSQLCommand>

              <SetOptions   ANSI_NULLS=ONANSI_NULL_DEFAULT=ONANSI_PADDING=ON

                            QUOTED_IDENTIFIER=ONENCRYPTED=FALSE />

              <CommandText>CREATE TABLE dbo.SalesYTD

              (

CalendarYear  VARCHAR(7),

                     SalesYTD      DECIMAL(10,2)

              )</CommandText>

       </TSQLCommand>

</EVENT_INSTANCE>

We can capture this information to a table for Auditing using a DDL Trigger.

First of all, we need to create a table to store the information that will be extracted from EVENTDATA XML. It can be created as below:

— Audit_Log will be used to capture DDL Activities

CREATE TABLE Audit_Log

(

       EventTime            DATETIME,

       LoginName            SYSNAME,

       UserName             SYSNAME,

       DatabaseName         SYSNAME,

       SchemaName           SYSNAME,

       ObjectName           SYSNAME,

       ObjectType           VARCHAR(50),

       DDLCommand           VARCHAR(MAX)

)

Result:

Command(s) completed successfully.

Now, we can create the actual Trigger to capture DDL Events, I have captured only CREATE / ALTER and DROP events for DDL, for a complete list you can refer the link above:

— © 2011 – Vishal (http://SqlAndMe.com)

 

USE SqlAndMe

 

CREATE TRIGGER Log_Table_DDL

ON DATABASE

–ON ALL SERVER – by creating it on Server level,

–you can capture below events for all databases on server

FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE

–Fires only for CREATE / ALTER / DROP Table

AS

DECLARE       @eventInfo XML

SET           @eventInfo = EVENTDATA()

 

INSERT INTO Audit_Log VALUES

(

       REPLACE(CONVERT(VARCHAR(50),

              @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),

       CONVERT(SYSNAME,

              @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),

       CONVERT(SYSNAME,

              @eventInfo.query('data(/EVENT_INSTANCE/UserName)')),

       CONVERT(SYSNAME,

              @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),

       CONVERT(SYSNAME,

              @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),

       CONVERT(SYSNAME,

              @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),

       CONVERT(VARCHAR(50),

              @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),

       CONVERT(VARCHAR(MAX),

              @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))

)

Result:

Command(s) completed successfully.

Once the Trigger is created, it will capture events in Audit_Log. Now let’s execute some DDL which can be captured:

USE SqlAndMe

 

CREATE TABLE MyTable

(

       ID_Number     INT,

)

 

ALTER TABLE MyTable

ADD Name VARCHAR(50)

 

DROP TABLE MyTable

 

CREATE VIEW v_Audit_Log AS

SELECT *

FROM   Audit_Log

 

Please note that the last statement creates a new VIEW, which will not be captured by Log_Table_DDL. Now let’s look at Audit_Log and see what has been captured:

SELECT *

FROM   Audit_Log

Result Set:

image

 

Hope This Helps!

Vishal