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.
https://gist.github.com/paschott/60e6cda8fc05f61566d92936caa08178
About Author
paschott
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others. I currently work for a Health-related non-profit based in Boise as a Database Architect.
Any chance you could zip up the scripts and link them here? Very nicely done solution!
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. 🙁
Nice one, I'd suggest to also add HOST_NAME()
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.
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.
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.
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.
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.
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.
Good catch, I've updated the process that creates the trigger to use Quoted_Identifier ON.