Home > SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > SQL Server – Auditing Schema Changes using DDL Triggers

SQL Server – Auditing Schema Changes using DDL Triggers

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

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

 

Advertisements
  1. May 18, 2012 at 2:42 am

    Hi Vishal, Thanks for posting this. I had to change a few things, but so far is working on SQL 2008 R2. I wanted full DDL auditing. SOme issues
    1)Microsoft apps, SCOM, Sharepoint, use database and table names with GUIDS or just really long schema names… I had to create many columns in the audit table as varchar(255) to allow writes.
    2)If you have a shared environment where many users have dbo to make their own DDL statements as db_owner or db_ddladmin then they will not be able to write to your shared table in another db. You need to modify the trigger to be with execute as ‘sa’ or similar for that to work.
    3)to capture all server wide and database wide DDL statements, use the MS catch-alls
    4)If you want to capture the Client IP, then use the spid …

    CREATE TRIGGER [Log_Table_DDL] ON ALL SERVER WITH EXECUTE AS ‘sa’
    FOR DDL_DATABASE_LEVEL_EVENTS,DDL_SERVER_LEVEL_EVENTS
    AS
    DECLARE @eventInfo XML
    DECLARE @client_net varchar(50)
    SET @eventInfo = EVENTDATA()

    set @client_net=(select client_net_address from master.sys.dm_exec_connections WHERE session_id = @@spid)

    INSERT INTO istops.dbo.audit_ddl_log VALUES

    (

    REPLACE(CONVERT(VARCHAR(50),

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

    CONVERT(VARCHAR(50),

    @eventInfo.query(‘data(/EVENT_INSTANCE/LoginName)’)),

    CONVERT(VARCHAR(255),

    @eventInfo.query(‘data(/EVENT_INSTANCE/UserName)’)),

    CONVERT(VARCHAR(255),

    ,@client_net

    Forrest, UC Berkeley

    • Forrest
      November 7, 2013 at 5:09 am

      I posted my code on this earlier, but I wanted to add one fix to it. It seems that some app installers have multiple connections during upgrades(lots of DDL changes), and the trigger can prevent them from succeeding. The fix is to change query mentioned above to select top 1 client_net_address from master.sys…. that way it will only return one ip regardless.

  2. Jason
    January 17, 2013 at 2:52 am

    Posted on: https://sqlandme.com/tag/eventdata-xml/

    Wanted to share my Add_extended_ properties

    CREATE TRIGGER [DB_Add_extended_properties]

    ON DATABASE

    FOR CREATE_TABLE,CREATE_VIEW, CREATE_PROCEDURE
    AS

    SET NOCOUNT ON
    DECLARE @eventInfo XML
    SET @eventInfo = EVENTDATA()

    DECLARE @timestamp DATETIME, @user VARCHAR(100), @ObjectType VARCHAR(50), @ObjectName VARCHAR(50)

    SELECT @timestamp=GETDATE()
    SELECT @user=SYSTEM_USER
    SET @ObjectType=CONVERT(VARCHAR(50),@eventInfo.query(‘data(/EVENT_INSTANCE/ObjectType)’))
    SET @ObjectName=CONVERT(VARCHAR(50),@eventInfo.query(‘data(/EVENT_INSTANCE/ObjectName)’))

    EXEC sys.sp_addextendedproperty @name=N’Created by’, @value=@user , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=@ObjectType ,@level1name=@ObjectName

    EXEC sys.sp_addextendedproperty @name=N’Purpose’, @value=N” , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=@ObjectType ,@level1name=@ObjectName

    EXEC sys.sp_addextendedproperty @name=N’Created on’, @value=@timestamp, @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=@ObjectType ,@level1name=@ObjectName
    GO

  3. Spanish Pinay
    August 2, 2013 at 5:24 pm

    For some raeson, this do no work in our environment. We are trying to store the DDL info to another database. Everytime a user from a different database creates an object we get the following error:

    The server principal “” is not able to access the database “DBAdmin” under the current security context.

    • Forrest
      November 7, 2013 at 5:15 am

      Likely, you need to use the with execute as ‘sa’ part of the trigger as I showed. I personally don’t think this is too much of a risk, but I suppose if someone could sql inject somehow in a DDL statement they were calling, it could be an issue. I just don’t think there is anyway to handle auditing of this type without doing this…your caller needs rights to the db to write audit events to at the very least. SQL server has some more sophisticated mechanims…event notifications service broker or such to try and accomplish the same things, but MS doesn’t seem to build this basic auditing mechanism out the box. You’d have to stick with 2005 era approach of DDL audits or try to google and find someone who has really accomplished this with server broker and such. Most of what I ever found on that on the web was theoretical and not fleshed out, but maybe a few have posted that more complex approach now.

  4. subhradeep bhowmik
    April 2, 2014 at 2:44 am

    Hi Vishal,
    People at our company copied your code and put the trigger onto a production server. Now, every time we try to deploy an object with a name longer than 50 characters, we get an error. Instead of fixing it, the dba’s want us to make all the names shorter.
    If you can update your web page and change the VARCHAR(50) data types to SYSNAME, we can show the dba’s that it is a correct fix.
    Thank you very much in advance for your assistance.

    • April 2, 2014 at 8:05 am

      Thanks. I have updated the post.

      • subhradeep bhowmik
        April 4, 2014 at 5:19 pm

        Thanks, Vishal, for your fast help!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: