Monday, February 8, 2010

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.

10 comments:

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

    ReplyDelete
  2. 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. :(

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

    ReplyDelete
  4. 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.

    ReplyDelete
  5. 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.

    ReplyDelete
  6. 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.

    ReplyDelete
  7. 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.

    ReplyDelete
    Replies
    1. 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.

      Delete
  8. 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.

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

    ReplyDelete