SQL Server Audit Schema Changes – Part 3: DDL Changes Logged to a Table Using Service Broker

Description In this first part of the SQL Server Audit Schema Changes series, we dive into how to track DDL (Data Definition Language) changes—such as CREATE, ALTER, and DROP statements—by capturing them and logging to a custom table. Learn how to leverage SQL Server’s native features, including DDL triggers and Service Broker, to build a lightweight, asynchronous auditing solution that doesn’t compromise performance. This post walks you through the architecture, setup, and practical implementation, laying the foundation for robust schema change tracking in your SQL Server environment.

USE DBA
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

  
CREATE VIEW SchemaAudit.[Retrieve_Schema_Change_Info] 
AS 
SELECT        TOP (100) PERCENT SchemaAudit.Objects.ServerName, SchemaAudit.Objects.DatabaseName, SchemaAudit.Objects.SchemaName,  
                         SchemaAudit.EventTypes.EventType AS ChangeType, SchemaAudit.Objects.ObjectName, SchemaAudit.Logins.LoginName AS ChangerloginName,  
                         SchemaAudit.Events.EventDate AS ModifiedDate  
                         ,XMLData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(8000)') AS CommandText 
                         ,SchemaAudit.EventData.EventDataXML 
FROM            SchemaAudit.EventData INNER JOIN 
                         SchemaAudit.Events ON SchemaAudit.EventData.EventID = SchemaAudit.Events.EventID INNER JOIN 
                         SchemaAudit.EventTypes ON SchemaAudit.Events.EventTypeID = SchemaAudit.EventTypes.EventTypeID INNER JOIN 
                         SchemaAudit.Logins ON SchemaAudit.Events.LoginID = SchemaAudit.Logins.LoginID INNER JOIN 
                         SchemaAudit.Objects ON SchemaAudit.Events.ObjectID = SchemaAudit.Objects.ObjectID 
                         CROSS APPLY EventDataXML.nodes('/EVENT_INSTANCE/TSQLCommand/CommandText') AS tbl (XMLData)
GO


----

USE DBA
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/* 
Description:
Trigger to populate SchemaAudit.Events, SchemaAudit.Objects, SchemaAudit.Logins and SchemaAudit.EventTypes when
DDL Event data is inserted into the SchemaAudit.EventData table

*/
CREATE TRIGGER [SchemaAudit].[tEventData_Insert] ON [SchemaAudit].[EventData]
AFTER INSERT
AS
SET NOCOUNT ON;
DECLARE @tEventData TABLE(
		EventID INT PRIMARY KEY,
		EventDate DATETIME NOT NULL,
		EventType VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		LoginName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		ObjectType VARCHAR(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		ServerName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		DatabaseName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		SchemaName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		ObjectName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		TargetObjectType VARCHAR(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		TargetServerName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		TargetDatabaseName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		TargetSchemaName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		TargetObjectName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		TargetObjectID INT NULL
);
-- shred XML data inserted into EventData table into tabular format
INSERT INTO @tEventData(EventID,
						EventDate,
						EventType,
						LoginName,
						ObjectType,
						ServerName,
						DatabaseName,
						SchemaName,
						ObjectName,
						TargetObjectType,
						TargetServerName,
						TargetDatabaseName,
						TargetSchemaName,
						TargetObjectName
						--TargetTSQLCommand,
						--TSQLCommand					
						)
SELECT EventID,
	EventDataXML.value('(/EVENT_INSTANCE/PostTime)[1]','DATETIME') as EventDate,
	ISNULL(EventDataXML.value('(/EVENT_INSTANCE/EventType)[1]','VARCHAR(50)'),'') as EventType,
	ISNULL(EventDataXML.value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(128)'),'') as LoginName,
	ISNULL(EventDataXML.value('(/EVENT_INSTANCE/ObjectType)[1]','VARCHAR(25)'),'') as ObjectType,
	ISNULL(EventDataXML.value('(/EVENT_INSTANCE/ServerName)[1]','NVARCHAR(128)'),'') as ServerName,
	ISNULL(EventDataXML.value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)'),'') as DatabaseName,
	--ISNULL(EventDataXML.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'),'') as TSQLCommand,
	-- Schema name isn't always recorded in XML event data.  It is logged for creating the schema, but not when granting permissions to the schema.
	-- For consistency, schema is blank for schema object types in all cases.
	CASE WHEN ISNULL(EventDataXML.value('(/EVENT_INSTANCE/ObjectType)[1]','VARCHAR(25)'),'') ='SCHEMA'
		THEN '' 
		ELSE ISNULL(EventDataXML.value('(/EVENT_INSTANCE/SchemaName)[1]','NVARCHAR(128)'),'') 
		END as SchemaName,
	ISNULL(EventDataXML.value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(128)'),'') as ObjectName,
	ISNULL(EventDataXML.value('(/EVENT_INSTANCE/TargetObjectType)[1]','VARCHAR(25)'),'') as TargetObjectType,
	ISNULL(EventDataXML.value('(/EVENT_INSTANCE/TargetServerName)[1]','NVARCHAR(128)'),
			ISNULL(EventDataXML.value('(/EVENT_INSTANCE/ServerName)[1]','NVARCHAR(128)'),'')) as TargetServerName,
	ISNULL(EventDataXML.value('(/EVENT_INSTANCE/TargetDatabaseName)[1]','NVARCHAR(128)'),
			ISNULL(EventDataXML.value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)'),'')) as TargetDatabaseName,
	ISNULL(EventDataXML.value('(/EVENT_INSTANCE/TargetSchemaName)[1]','NVARCHAR(128)'), 
			ISNULL(EventDataXML.value('(/EVENT_INSTANCE/SchemaName)[1]','NVARCHAR(128)'),'')) as TargetSchemaName,
	ISNULL(EventDataXML.value('(/EVENT_INSTANCE/TargetObjectName)[1]','NVARCHAR(128)'),'') as TargetObjectName
	--ISNULL(EventDataXML.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'),'') as TargetTSQLCommand
FROM INSERTED;

UPDATE @tEventData
	SET ObjectType = 'SCHEMA',
	ObjectName = ISNULL(NULLIF(ObjectName,''),SchemaName),
	SchemaName = ''
WHERE EventType IN('DROP_SCHEMA','CREATE_SCHEMA')

UPDATE @tEventData
	SET ObjectType = 'DATABASE',
	 ObjectName = DatabaseName
WHERE EventType IN('CREATE_DATABASE','DROP_DATABASE','ALTER_DATABASE')

-- Create rows in objects table for target objects (e.g. index or trigger would have target object as table or view etc).
INSERT INTO SchemaAudit.Objects(ObjectType,ServerName,DatabaseName,SchemaName,ObjectName)--,TSQLCommand)
SELECT TargetObjectType,ISNULL(TargetServerName,ServerName),ISNULL(TargetDatabaseName,DatabaseName),ISNULL(TargetSchemaName,SchemaName),TargetObjectName--,ISNULL(TargetTSQLCommand,TSQLCommand)
FROM @tEventData
WHERE TargetObjectName <> ''
EXCEPT
SELECT ObjectType,ServerName,DatabaseName,SchemaName,ObjectName--,TSQLCommand
FROM SchemaAudit.Objects;

-- Get TargerObjectID value for events with a target object
UPDATE ED 
	SET ED.TargetObjectID = OT.ObjectID
FROM @tEventData ED
JOIN SchemaAudit.Objects OT ON OT.DatabaseName =ED.TargetDatabaseName
									AND OT.ObjectName = ED.TargetObjectName
									AND OT.ObjectType = ED.TargetObjectType
									AND OT.SchemaName = ED.TargetSchemaName
									AND OT.ServerName = ED.TargetServerName
									--AND OT.TSQLCommand =ED.TSQLCommand
WHERE ED.TargetObjectName <> '';

-- Create rows in objects table if they don't already exist for the event data inserted
INSERT INTO SchemaAudit.Objects(ObjectType,ServerName,DatabaseName,SchemaName,ObjectName,TargetObjectID)--,TSQLCommand)
SELECT ED.ObjectType,ED.ServerName,ED.DatabaseName,ED.SchemaName,ED.ObjectName,ED.TargetObjectID--,ED.TSQLCommand
FROM @tEventData ED
EXCEPT
SELECT ObjectType,ServerName,DatabaseName,SchemaName,ObjectName,TargetObjectID--,TSQLCommand
FROM SchemaAudit.Objects;

-- Insert event types for event data inserted if they don't already exist
INSERT INTO SchemaAudit.EventTypes(EventType,ObjectType)
SELECT DISTINCT EventType,ObjectType
FROM @tEventData
EXCEPT
SELECT EventType,ObjectType
FROM SchemaAudit.EventTypes;

-- Insert login names associated with event data inserted if they don't already exist
INSERT INTO SchemaAudit.Logins(LoginName)
SELECT LoginName 
FROM @tEventData
EXCEPT
SELECT LoginName
FROM SchemaAudit.Logins;



-- Insert data into events table ("dimension" tables populated so can JOIN on unique key to get surrogate keys for insert into fact table)
INSERT INTO SchemaAudit.Events(EventID,EventDate,LoginID,ObjectID,EventTypeID)
SELECT A.EventID,A.EventDate,L.LoginID,O.ObjectID,ET.EventTypeID
FROM @tEventData A
JOIN SchemaAudit.Logins L ON A.LoginName = L.LoginName
JOIN SchemaAudit.Objects O ON A.ObjectType = O.ObjectType 
								AND A.ServerName = O.ServerName
								AND A.DatabaseName = O.DatabaseName 
								AND A.SchemaName = O.SchemaName 
								AND A.ObjectName = O.ObjectName
								--AND A.TSQLCommand = O.TSQLCommand
								AND ((A.TargetObjectID = O.TargetObjectID)
									OR (A.TargetObjectID IS NULL AND O.TargetObjectID IS NULL)
									)
JOIN SchemaAudit.EventTypes ET ON A.EventType  = ET.EventType AND A.ObjectType = ET.ObjectType;


GO

ALTER TABLE [SchemaAudit].[EventData] ENABLE TRIGGER [tEventData_Insert]
GO

——-TEST