DDL Schema Change Auditing on SQL Server 2005 / 2008

I’ve run across this before so wanted to write up a solution to keeping track of schema changes we’ve used. We trust our team to make DDL changes, additions, drops in order to get their job done. We also trust them to save those scripts into source control as they are run so we can use them when we release software. However, there are times that people make changes and forget to save the script or just think that it’s a temporary change to test and forget about it. With that in mind, we decided to take advantage of SQL Server 2005’s DDL triggers.
First, we created a database called [Audit] on our development server.
Next, we created a table to store the logs.

USE Audit

GO

CREATE TABLE [dbo].[DDL_Audit](

[DDL_Audit_ID] [int] IDENTITY(1,1) NOT NULL,

[Event_Type] [varchar](100) NULL,

[Database_Name] [varchar](100) NULL,

[SchemaName] [varchar](100) NULL,

[ObjectName] [varchar](100) NULL,

[ObjectType] [varchar](100) NULL,

[EventDate] [datetime] NULL,

[SystemUser] [varchar](100) NULL,

[CurrentUser] [varchar](100) NULL,

[OriginalUser] [varchar](100) NULL,

[EventDataText] [varchar](max) NULL

) ON [PRIMARY]


GO

GRANT INSERT ON DDL_Audit TO public

GO

Note in this example, I granted INSERT permissions to public to avoid needing to give our team any other access to that database. I didn’t want them to read/write rows in that table if I could avoid it. After that, I ran a script in SSMS using Text output to step through all of our databases and generate the trigger create code.

sp_msforeachdb 'SELECT ''use ?

GO

SET ANSI_PADDING ON

GO

SET QUOTED_IDENTIFIER ON

GO



CREATE TRIGGER trg_DDL_Monitor_Change
ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

SET NOCOUNT ON

SET ANSI_PADDING ON


declare @EventType varchar(100)

declare @SchemaName varchar(100)

declare @DatabaseName varchar(100)

declare @ObjectName varchar(100)

declare @ObjectType varchar(100)

DECLARE @EventDataText VARCHAR(MAX)


SELECT 

@EventType = EVENTDATA().value(''''(/EVENT_INSTANCE/EventType)[1]'''',''''nvarchar(max)'''')  

,@DatabaseName = EVENTDATA().value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''',''''nvarchar(max)'''')  

,@SchemaName = EVENTDATA().value(''''(/EVENT_INSTANCE/SchemaName)[1]'''',''''nvarchar(max)'''')  

,@ObjectName = EVENTDATA().value(''''(/EVENT_INSTANCE/ObjectName)[1]'''',''''nvarchar(max)'''')

,@ObjectType = EVENTDATA().value(''''(/EVENT_INSTANCE/ObjectType)[1]'''',''''nvarchar(max)'''')   

,@EventDataText = EVENTDATA().value(''''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'''',''''nvarchar(max)'''')


insert into Audit.dbo.DDL_Audit (Event_Type, Database_Name, SchemaName, ObjectName, ObjectType

, EventDate, SystemUser, CurrentUser, OriginalUser, EventDataText)

select @EventType, @DatabaseName, @SchemaName, @ObjectName, @ObjectType

, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN()

, @EventDataText

GO

'''

We took the results of that script and removed any databases that did not matter for purposes of tracking changes. We ran that and created the appropriate triggers on all of the databases. That left us with the notification messages. We handled this through a SQL Server Agent job using Database Mail.

IF EXISTS (SELECT 'x' FROM Audit.dbo.DDL_Audit

WHERE Event_Type NOT LIKE '%statist%'

AND SystemUser NOT IN (service account list here)

AND EventDate >= convert(varchar(10),DATEADD(dd, -1, GETDATE()),101))

BEGIN

DECLARE @email_from nvarchar(100)

, @email_address nvarchar(200) 

, @TheSubject nvarchar(255)


SELECT @Email_Address = 'myemail@myemail.com'

SET @email_from = 'DevServer@server.com'
select @email_address as 'To:' ,  @email_from as 'From:'


set @TheSubject = 'Recent Schema changes on ' + RTRIM(@@SERVERNAME)

DECLARE @tableHTML  NVARCHAR(MAX) ;


SET @tableHTML =

N'<H1>DevDB Schema Change</H1>' +

N'<table border="1">' +

N'<tr><th>Database_Name</th><th>SchemaName</th>' +

N'<th>ObjectName</th><th>Event_Type</th><th>ObjectType</th>' +

N'<th>EventDate</th><th>SystemUser</th><th>CurrentUser</th><th>OriginalUser</th><th>EventDataText</th></tr>' +

CAST ( ( SELECT td = Database_Name,       '',

td = SchemaName, '',

td = ObjectName, '',

td = Event_Type, '',

td = ObjectType, '',

td = EventDate, '',

td = SystemUser, '',

td = CurrentUser, '',

td = OriginalUser, '',

td = EventDataText

FROM Audit.dbo.DDL_Audit

WHERE Event_Type NOT LIKE '%statist%'

AND SystemUser NOT IN (serviceaccount)

AND EventDataText not like '%ALTER%INDEX%REBUILD%'

AND EventDate >= convert(varchar(10),DATEADD(dd, -1, GETDATE()),101)

ORDER BY Database_Name, ObjectType, ObjectName, EventDate, Event_Type

FOR XML PATH('tr'), TYPE 

) AS NVARCHAR(MAX) ) +

N'</table>' ;


EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default' ,

@recipients=@email_address,

@subject = @TheSubject,

@body = @tableHTML,

@body_format = 'HTML' ;


END

We scheduled this code in a job step that runs daily at 10am. Any changes in that time frame were then sent to a team of people on a daily basis. If no changes were made, no email was sent. Hopefully this will give someone some ideas on one way to audit changes to their DBs. I make no claims that this is the best way, but it worked well for us.

You can download the script file containing all of this code using the link below.