Logging schema changes using DDL trigger

Logging schema changes (DDL) in the database is a common requirement in many organizations or development teams. We can choose from many tools offering complex auditing or write our solution. Let’s build our one and keep it as simple as possible. Please remember that the event log can be manipulated by everyone with access to the database and this solution isn’t suitable for security compliant auditing.

Our solution is based on a DDL trigger. You can read more about it in BOL.

Create table [dbo].[DDLEventLog] where logged events will be stored. Create trigger [trg_DatabaseDDLeventer] which will fire on all database level DDL events ( DDL_DATABASE_LEVEL_EVENTS) and store event detail in XML format to [dbo].[DDLEventLog] table.

-- Create events table
CREATE TABLE [dbo].[DDLEventLog]
(
    [Id] INT IDENTITY(1,1) PRIMARY KEY,
    [EventDate] DATETIME,
    [Originator] NVARCHAR(256),
    [EventXML] XML
)
GO

-- Create trigger
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [trg_DatabaseDDLeventer] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
    
	INSERT INTO [dbo].[DDLEventLog] 
        ([EventDate], [Originator], [EventXML])
    VALUES
        (GETDATE(), USER_NAME(), EVENTDATA())

END
GO

We will run the DDL statement CREATE TABLE to test how it works.

-- Create sample DDL event to test trigger
CREATE TABLE [dbo].[TestTable] ([Id] int)
GO

-- Get results
SELECT * FROM [DDLEventLog]
GO

If we will click on the link in EventXML column we can observe message details and identify who executed which DDL statement:

<EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType>
  <PostTime>2010-07-11T10:55:04.710</PostTime>
  <SPID>51</SPID>
  <ServerName>TESTSERVER</ServerName>
  <LoginName>TESTSERVER\Admin</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>master</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>TestTable</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE TABLE dbo.TestTable (Id int)</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

If you would like to implement a more robust and security compliant solution then please review SQL Server Audit functionality for more details.