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.
----Store Procs
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Description:
Return a list of databases associated with DDL events or DDL snapshots in this database
*/
CREATE PROC [SchemaAudit].[Databases_Get]
AS
SET NOCOUNT ON;
SELECT DISTINCT DatabaseName
FROM SchemaAudit.Objects
ORDER BY DatabaseName;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Description:
Insert XML generated by DDL Event into SchemaAudit.EventData table. A trigger exists
on the EventData table to add the event to the SchemaAudit.Event table etc.
*/
CREATE PROC [SchemaAudit].[DDLEvent_Insert](@EventData XML)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;
INSERT INTO SchemaAudit.EventData(EventDataXML)
VALUES(@EventData);
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Description:
Return a list of databases that are tracking DDL Events with event notifications
broker_instance specified in event notification is returned so this can be compared to
broker_instance of SchemaAudit database.
*/
CREATE PROC [SchemaAudit].[DDLEventNotificationDBs_Get]
AS
CREATE TABLE #EN(
db SYSNAME NOT NULL PRIMARY KEY,
broker_instance UNIQUEIDENTIFIER,
ErrorMessage NVARCHAR(2048)
);
DECLARE @db SYSNAME;
DECLARE @SQL NVARCHAR(MAX);
DECLARE cDatabases CURSOR LOCAL FAST_FORWARD
FOR SELECT name
FROM sys.databases
WHERE state=0 --ONLINE
AND user_access = 0 --MULTI_USER
AND is_read_only = 0
AND name NOT IN('master','model','msdb')
AND is_broker_enabled = 1;
OPEN cDatabases;
WHILE 1=1
BEGIN;
FETCH NEXT FROM cDatabases INTO @db;
IF @@FETCH_STATUS <> 0
BEGIN;
BREAK;
END;
SET @SQL=N'USE ' + QUOTENAME(@db) + '
INSERT INTO #EN(db,broker_instance)
SELECT ' + QUOTENAME(@db,'''') + ',broker_instance
FROM sys.event_notifications
WHERE name = ''SchemaAudit_DDLEvent_Notification''
AND service_name = ''SchemaAudit_DDLEventService''
';
BEGIN TRY;
EXEC sp_executesql @SQL;
END TRY
BEGIN CATCH;
--Error might occur if user doesn't have access to the database
INSERT INTO #EN(db,ErrorMessage)
SELECT @db,ERROR_MESSAGE();
END CATCH;
END;
CLOSE cDatabases;
DEALLOCATE cDatabases;
SELECT db,broker_instance,ErrorMessage
FROM #EN;
DROP TABLE #EN;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Description:
Return broker_instance for server level event notification if it exists
*/
CREATE PROC [SchemaAudit].[DDLEventNotificationServer_Get]
AS
SELECT broker_instance
FROM sys.server_event_notifications
WHERE name = 'SchemaAudit_DDLEvent_Notification'
AND service_name = 'SchemaAudit_DDLEventService';
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [SchemaAudit].[DDLEventQueue_Receive]
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @tDDLEvents TABLE(
EventDataXML XML
);
--Keep processing messages until timeout (no more messages to process)
WHILE 1=1
BEGIN;
-- Transaction includes rcv msgs, process, and write to table
BEGIN TRAN;
-- Get next 1000 messages from queue. Timeout after 1 second if no messages are on the queue
WAITFOR (RECEIVE TOP(1000) message_body
FROM SchemaAudit.DDLEvent_Queue
INTO @tDDLEvents
), TIMEOUT 1000;
-- Exit if not messages returned
IF @@ROWCOUNT = 0
BEGIN;
ROLLBACK TRAN;
BREAK;
END;
-- A trigger will fire on insert into EventData, shredding the XML data and populating various other tables (events, objects etc)
INSERT INTO SchemaAudit.EventData(EventDataXML)
SELECT EventDataXML
FROM @tDDLEvents
WHERE EventDataXML IS NOT NULL;
-- Empty table variable ready to process next batch of events
DELETE FROM @tDDLEvents;
COMMIT TRAN;
END;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Description:
Return the data that was captured for a specific DDL event in it's original XML format
*/
CREATE PROC [SchemaAudit].[EventData_Get](
@EventID INT
)
AS
SET NOCOUNT ON;
SELECT EventDataXML
FROM SchemaAudit.EventData
WHERE EventID = @EventID;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Description:
Return a list of event types e.g. (CREATE PROCEDURE, ALTER TABLE etc)
*/
CREATE PROC [SchemaAudit].[EventTypes_Get](
@ObjectTypes XML=NULL
)
AS
SET NOCOUNT ON;
DECLARE @tObjectTypes TABLE(
ObjectType VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL PRIMARY KEY
);
IF @ObjectTypes IS NOT NULL
BEGIN;
INSERT INTO @tObjectTypes(ObjectType)
SELECT T.c.value('.','VARCHAR(50)')
FROM @ObjectTypes.nodes('/items/item') as T(c);
SELECT DISTINCT EventType
FROM SchemaAudit.EventTypes ET
JOIN @tObjectTypes OT ON ET.ObjectType = OT.ObjectType
ORDER BY EventType;
END;
ELSE
BEGIN
SELECT DISTINCT EventType
FROM SchemaAudit.EventTypes
ORDER BY EventType;
END;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Description:
Return a distinct list of Login Names from SchemaAudit.Logins table
*/
CREATE PROC [SchemaAudit].[Logins_Get]
AS
SET NOCOUNT ON;
SELECT DISTINCT LoginName
FROM SchemaAudit.Logins
ORDER BY LoginName;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Description:
Search for a list of object names matching the specified filter. Used to produce a
selection list of object names in the filter dialog
*/
CREATE PROC [SchemaAudit].[Objects_Get](
@Databases XML=NULL,
@Schemas XML=NULL,
@ObjectTypes XML=NULL
)
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @NewLine NVARCHAR(MAX);
SET @NewLine = N'
';
SET @SQL = N'
DECLARE @tDatabases TABLE(
DatabaseName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL PRIMARY KEY
);
DECLARE @tSchemas TABLE(
SchemaName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL PRIMARY KEY
);
DECLARE @tObjectTypes TABLE(
ObjectType VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL PRIMARY KEY
);
';
IF @Databases IS NOT NULL
BEGIN;
SET @SQL = @SQL + N'
INSERT INTO @tDatabases(DatabaseName)
SELECT T.c.value(''.'',''NVARCHAR(128)'')
FROM @Databases.nodes(''/items/item'') as T(c);
';
END;
IF @Schemas IS NOT NULL
BEGIN;
SET @SQL = @SQL + N'
INSERT INTO @tSchemas(SchemaName)
SELECT T.c.value(''.'',''NVARCHAR(128)'')
FROM @Schemas.nodes(''/items/item'') as T(c)
';
END;
IF @ObjectTypes IS NOT NULL
BEGIN;
SET @SQL = @SQL + N'
INSERT INTO @tObjectTypes(ObjectType)
SELECT T.c.value(''.'',''VARCHAR(50)'')
FROM @ObjectTypes.nodes(''/items/item'') as T(c)
';
END;
SET @SQL = @SQL + N'
SELECT DISTINCT ObjectName
FROM SchemaAudit.Objects O
WHERE 1=1
'
+ CASE WHEN @Databases IS NOT NULL THEN @NewLine + 'AND EXISTS(SELECT 1 FROM @tDatabases t WHERE t.DatabaseName = O.DatabaseName)' ELSE '' END
+ CASE WHEN @Schemas IS NOT NULL THEN @NewLine + 'AND EXISTS(SELECT 1 FROM @tSchemas t WHERE t.SchemaName = O.SchemaName)' ELSE '' END
+ CASE WHEN @ObjectTypes IS NOT NULL THEN @NewLine + 'AND EXISTS(SELECT 1 FROM @tObjectTypes t WHERE t.ObjectType = O.ObjectType)' ELSE '' END +
'ORDER BY ObjectName';
EXEC sp_executesql @SQL,N'@Databases XML,@Schemas XML,@ObjectTypes XML',@Databases,@Schemas,@ObjectTypes;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Description:
Return a list of object types. Used to produce a
selection list of object types in the filter dialog
*/
CREATE PROC [SchemaAudit].[ObjectTypes_Get]
AS
SET NOCOUNT ON;
SELECT DISTINCT ObjectType
FROM SchemaAudit.EventTypes
ORDER BY ObjectType;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Use if you want email sent out or send to teams
*/
CREATE PROCEDURE [SchemaAudit].[retrieveschema_changeinfo]
@MIN INT -- NUMBER OF MINUTES YOU WANT TO GO BACK AND CHECK FOR CHANGES
AS
SET QUOTED_IDENTIFIER ON
DECLARE
@RecipientEmailAddress NVARCHAR(2048),
@EmailSubject NVARCHAR(1024),
@LAST_RUN_DATE datetime,
@tableHTML NVARCHAR(MAX),
@Style NVARCHAR(MAX),
@ServerName sysname,
@Subject_msg VARCHAR(8000),
@Subject_msgbody VARCHAR(8000),
@MsgFilterList VARCHAR(8000),
@UseNotLike BIT = 1,
@UseOr BIT = 0,
@SQL VARCHAR(8000),
@Body VARCHAR(MAX),
@message VARCHAR(256),
@dbmail_body NVARCHAR(MAX),
@DBMailProfileName varchar(100),
@dbmail_recipients varchar(100)
Select @DBMailProfileName=name
from msdb.dbo.sysmail_profile
SET @RecipientEmailAddress = 'email';
SET @ServerName = @@SERVERNAME;
SET @Subject_msg = 'SchemaAudit have Occurred on ' + @ServerName;
SET @DBMailProfileName = 'name';
--- SET @dbmail_recipients = 'email';
CREATE TABLE #SchemaAudit
(ServerName sysname,
DatabaseName sysname,
SchemaName varchar(256),
ChangeType varchar(256),
ObjectName varchar(256),
ChangerloginName varchar(256),
ModifiedDate datetime)
SELECT @MsgFilterList = ''
--1st pass
SELECT @MsgFilterList = @MsgFilterList + CHAR(10)+ CHAR(13) +
CASE WHEN @UseOr = 0 THEN 'AND ' ELSE 'OR ' END + 'CommandText ' +
CASE WHEN @UseNotLike = 1 THEN 'NOT LIKE ''' ELSE 'LIKE ''' END + Exclude_TSQL_Command_Pattern + ''''
FROM SchemaAudit.ExcludedTSQLs as lf
--2nd pass
SELECT @MsgFilterList = @MsgFilterList + CHAR(10)+ CHAR(13) +
CASE WHEN @UseOr = 0 THEN 'AND ' ELSE 'OR ' END + 'ChangerloginName ' +
CASE WHEN @UseNotLike = 1 THEN 'NOT LIKE ''' ELSE 'LIKE ''' END + LoginName + ''''
FROM SchemaAudit.ExcludeLogins
SELECT @MsgFilterList = SUBSTRING(@MsgFilterList, 6, DATALENGTH(@MsgFilterList))
SELECT @MsgFilterList = @MsgFilterList + CHAR(10)+ CHAR(13) + 'AND ModifiedDate >= DATEADD(MINUTE, -' + CONVERT(varchar(15), @MIN) + ', CURRENT_TIMESTAMP)'
SET @SQL = 'INSERT INTO #SchemaAudit (ServerName,
DatabaseName,
SchemaName,
ChangeType,
ObjectName,
ChangerloginName,
ModifiedDate)
SELECT [ServerName]
,[DatabaseName]
,[SchemaName]
,[ChangeType]
,[ObjectName]
,[ChangerloginName]
,[ModifiedDate]
FROM SchemaAudit.[Retrieve_Schema_Change_Info] t
WHERE ' + @MsgFilterList
--SELECT @SQL
EXEC (@SQL)
IF (SELECT COUNT(*)
FROM #SchemaAudit) > 0
BEGIN
SET @tableHTML =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
table width=100%;
font-size: 12px;
text-align: left;
border-collapse: collapse;
border-top: 5px solid #267EAE;
border-bottom: 5px solid #267EAE;
}
#box-table th
{
table width=100%;
font-size: 12px;
font-weight: normal;
background: IndianRed;
border-right: 1px solid #267EAE;
border-left: 1px solid #267EAE;
border-bottom: 1px solid #267EAE;
color: Black;
}
#box-table tr
{
table width=100%;
font-size: 13px;
font-weight: normal;
background: Lavender;
border-right: 1px solid #267EAE;
border-left: 1px solid #267EAE;
border-bottom: 1px solid #267EAE;
color: #003399;
}
#box-table td
{
table width=100%;
border-right: 1px solid #267EAE;
border-left: 1px solid #267EAE;
border-bottom: 1px solid #267EAE;
color: Black;
}
tr:nth-child(odd) { background-color:#000000; }
tr:nth-child(even) { background-color:#ffffff; }
</style>'+
N'<table id="box-table" >' +
N'
<td><table width=100%><font face=Lucida Sans Unicode color=#003399 size=4><strong>'+@Subject_msg+'</strong></font></td
</style>'+
N'<table id="box-table" >' +
N'
<th>ServerName</th>
<th>DatabaseName</th>
<th>SchemaName</th>
<th>ChangeType</th>
<th>ObjectName</th>
<th>ChangerloginName</th>
<th>ModifiedDate</th>'+
CAST ( ( SELECT td = ServerName, '',
td = DatabaseName, '',
td = SchemaName, '',
td = ChangeType, '',
td = ObjectName, '',
td = ChangerloginName, '',
td = ModifiedDate, ''
FROM #SchemaAudit
ORDER BY ModifiedDate DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
SET @tableHTML = REPLACE(REPLACE(@tableHTML, '<', '<'), '>', '>')+ '</table>'
--SET @dbmail_body = @message + @tableHTML
EXEC msdb.dbo.sp_send_dbmail
@profile_name =@DBMailProfileName,
@recipients= @RecipientEmailAddress,
--@blind_copy_recipients='enteremail',
@subject = @Subject_msg,
@body = @tableHTML,
@body_format = 'HTML' ;
DROP TABLE #SchemaAudit
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Description:
Return a list of schemas. Used to produce a
selection list of schemas in the filter dialog
*/
CREATE PROC [SchemaAudit].[Schemas_Get](
@Databases XML=NULL
)
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @NewLine NVARCHAR(MAX);
SET @NewLine = N'
';
SET @SQL = N'
DECLARE @tDatabases TABLE(
DatabaseName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL PRIMARY KEY
)
';
IF @Databases IS NOT NULL
BEGIN;
SET @SQL = @SQL + N'
INSERT INTO @tDatabases(DatabaseName)
SELECT T.c.value(''.'',''NVARCHAR(128)'')
FROM @Databases.nodes(''/items/item'') as T(c)
';
END;
SET @SQL = @SQL + N'
SELECT DISTINCT SchemaName
FROM SchemaAudit.Objects O
WHERE 1=1
' + CASE WHEN @Databases IS NOT NULL THEN @NewLine + 'AND EXISTS(SELECT 1 FROM @tDatabases t WHERE t.DatabaseName = O.DatabaseName)' ELSE '' END + '
ORDER BY SchemaName;';
EXEC sp_executesql @SQL,N'@Databases XML',@Databases;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Description:
Return a list of server names associated with DDL operations or DDL snapshots that exist within this database
Normally this would just return the name of the current database instance
*/
CREATE PROC [SchemaAudit].[Servers_Get]
AS
SET NOCOUNT ON;
SELECT DISTINCT ServerName
FROM SchemaAudit.Objects
ORDER BY ServerName;
GO
Discover more from SQLYARD
Subscribe to get the latest posts sent to your email.
