How to audit Database Design Modifications using a DDL Trigger

September 27, 2012 · Posted in SQL, Technology 

Recently we identified the need to create a system for tracking changes made to our databases by developers and internal systems. Although the database itself is using FULL logging, which allows you to view changes made, we found that we’d like to see all modifications made over the entire history of the database in question. This prompted me to develop a table containing all changes that could be easily queried. The result of that work is below.


CREATE TABLE dbo.DesignLog(
    DesignLogID int NOT NULL PRIMARY KEY CONSTRAINT PK_DesignLog IDENTITY(1,1),
    DateStamp datetime NULL CONSTRAINT DF_DesignLog_DateStamp DEFAULT (getdate()),
    HostName nvarchar(255) NULL CONSTRAINT DF_DesignLog_HostName DEFAULT (host_name()),
    UserName nvarchar(255) NULL CONSTRAINT DF_DesignLog_UserName DEFAULT (suser_sname()),
    ActionType nvarchar(255) NULL,
    StatementExecuted nvarchar(max) NULL
);
GO
CREATE TRIGGER [DesignLogTrigger]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
    /*
        Authored By: Max Vernon
        Author Date:    2012-07-05
        Purpose:        Capture DDL Statements executed against database
    */
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @ActionType nvarchar(max);
    DECLARE @Statement nvarchar(max);
    DECLARE @msg nvarchar(max);
    SET @data = EVENTDATA();
    SET @msg = cast(@data.query('(/EVENT_INSTANCE/TSQLCommand/CommandText)') as nvarchar(max));
    SET @msg = dbo.xmltostring(@data.query('(/EVENT_INSTANCE/TSQLCommand/CommandText)'));
    SET @msg = dbo.RemoveTag(@msg, '');
    SET @msg = dbo.RemoveTag(@msg, '');
    SET @ActionType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)');
    SET @Statement = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)');
    IF @msg NOT LIKE 'UPDATE STATISTICS' + char(37) + ''
        AND @msg NOT LIKE 'ALTER INDEX' + char(37) + 'REORGANIZE' + char(37) + ''
        AND @msg NOT LIKE 'ALTER INDEX' + char(37) + 'REBUILD' + char(37) + ''
    BEGIN
        BEGIN TRAN;
        INSERT INTO DesignLog
            (
             ActionType
            , StatementExecuted
            )
        VALUES
            (
             @ActionType
            , @Statement
            );
        COMMIT TRAN;
    END
END;
GO
ENABLE TRIGGER [DesignLogTrigger] ON DATABASE;

This DDL Trigger does not log certain events such as UPDATE STATISTICS, INDEX REBUILD, AND INDEX REORGANIZE. We are not interested in logging these events since these events happen during our nightly database maintenance tasks, and therefore crowd out the meaningful changes made by developers.

The advantage of this particular trigger is that it captures the actual SQL text of the event, thereby creating a log of changes made to all objects in the database. This allows you to revert back to a prior version of any given object – a sort of manual revision control system.

The XMLUnescaper.XMLtoString function source code is written in VB.Net and uploaded to the SQL Server as a CLR function. That source code is:


Option Explicit On

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions
     _
    Public Shared Function XMLToString(ByVal XML As System.Data.SqlTypes.SqlXml) As System.Data.SqlTypes.SqlString

        Dim sXML As String = XML.Value
        Dim sTemp1 As String
        Dim sTemp2 As String
        Dim sTemp3 As String
        Dim a As Int32
        Dim b As Int32
        Dim c As Int32
        Dim s As String

        Try
            sXML = Replace(sXML, ">", ">")
            sXML = Replace(sXML, "<", "<")             sXML = Replace(sXML, "&", "&")             sXML = Replace(sXML, """, Chr(34))
            For a = 1 To Len(sXML)
                b = InStr(a, sXML, "&#x")
                If b > 0 Then
                    c = InStr(b, sXML, ";")
                    s = Mid(sXML, b + 3, c - b - 3)
                    sTemp1 = Left(sXML, b - 1)
                    sTemp2 = Chr("&H" & s)
                    sTemp3 = Right(sXML, Len(sXML) - (c + 1))
                    sXML = sTemp1 & sTemp2 & sTemp3
                Else
                    Exit For
                End If
            Next
        Catch ex As Exception
            sXML = ex.ToString & vbCrLf & vbCrLf & "Len(XML) = " & Len(XML) & vbCrLf & vbCrLf & sXML
        End Try

        Return sXML
    End Function

     _
    Public Shared Function RemoveTag( ByVal XML As String, ByVal TagToRemove As String) As System.Data.SqlTypes.SqlString

        Return Replace(XML, TagToRemove, "")

    End Function

End Class

A binary version of XMLUnescaper is available from http://www.mvct.com/sql-server/XMLUnescaper.zip

Comments

2 responses to “How to audit Database Design Modifications using a DDL Trigger”

  1. Abid says:

    Getting This error
    Msg 4121, Level 16, State 1, Procedure DesignLogTrigger, Line 27
    Cannot find either column “dbo” or the user-defined function or aggregate “dbo.xmltostring”, or the name is ambiguous.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please answer the anti-spam question: * Time limit is exhausted. Please reload CAPTCHA.