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.