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.  See complete code at the end of this post.

First, we created a database called [Audit] on our development server.
Next, we created a table to store the logs.

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.

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.

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.

———————————————————–
Create Audit Database
———————————————————–
USE master
GO
CREATE DATABASE Audit
GO
USE [Audit]
GO
/****** Object: Table [dbo].[DDL_Audit] Script Date: 07/23/2007 12:28:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
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
———————————————————–
Initial script to generate the auditing framework for all databases
Run in Text output mode
Remove any databases you do not want to audit.
Copy resulting text into a new SQL query and run to create audit triggers
———————————————————–
sp_msforeachdb 'SELECT ''use ?
GO
SET ANSI_PADDING 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
'''
———————————————————–
Script used when e-mailing change results
Modify and use in a SQL Agent job w/ Database Mail to e-mail HTML results
———————————————————–
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' change to desired recipients
SET @email_from = 'DevServer@server.com' change if needed for your server
select @email_address as 'To:' , @email_from as 'From:'
set @TheSubject = 'Recent Schema changes on ' + RTRIM(@@SERVERNAME)
DECLARE @tableHTML NVARCHAR(MAX) ;
Modify query as needed to change results you see.
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

Comments
  1. Jorge Segarra

    Any chance you could zip up the scripts and link them here? Very nicely done solution!

  2. Peter Schott

    I'll see if I can zip up the files and save in some way. I haven't done that yet on Blogger. The code _is_ all there, but you have to copy it and paste it as plain text. That's a little tricky as the code snippet plugin I tried for LiveWriter didn't quite do what I expected. 🙁

  3. Anonymous

    Nice one, I'd suggest to also add HOST_NAME()

  4. Anonymous

    Hi,
    I have created this trigger in all the user DB's. The users were not able to create a objects in the DB's. The trigger is throwing error that they don't have privilege to insert in the audit DB. Please let me know why this happens.

  5. Peter Schott

    You may be able to work around that by giving “INSERT” rights on the DDL_Audit table to “public”. That way any user will be able to write to the table. I think this is what I did when I set this up initially as we had the same problem.

  6. Spanish Pinay

    Hi, I am ecountering the same error as second Anonymous commenter. I already granted INSERT to public but the only way to make it work is to add the user to the AUDIT database which is not what we wanted. It'll be too much of maintenance task to make sure that all users on all other databases are in the AUDIT database.

    please help..

    Thanks.

  7. Spanish Pinay

    Hi, I am ecountering the same error as second Anonymous commenter. I already granted INSERT to public but the only way to make it work is to add the user to the AUDIT database which is not what we wanted. It'll be too much of maintenance task to make sure that all users on all other databases are in the AUDIT database.

    please help..

    Thanks.

  8. Peter Schott

    You may want to try granting INSERT to guest as well (and enabling the Guest account for that DB). It's possible that if the guest access is disabled, that could be part of the issue. I've also seen strange issues when someone has a user account in the DB that isn't correctly linked to their login.

  9. Mark Hill

    I deployed the trigger to multiple databases using a SQLCMD batch script which did not have SET QUOTED_IDENTIFIER ON, when the trigger fired it failed with the following error.

    Msg 1934, Level 16, State 1, Procedure trg_DDL_Monitor_Change, Line 15
    SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

  10. Peter Schott

    Good catch, I've updated the process that creates the trigger to use Quoted_Identifier ON.

Leave a Reply

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