We recently had a problem where someone modified a stored procedure on one of our production systems and, the next time it ran, it caused an outage that lasted for about an hour while we tracked down what had happened. We we never able to figure out who made the change or when it was made (this process only runs once every three months), so we decided to implented some logging to track changes. I opted to go with a database level DDL trigger that would capture changes to schemas and log them to a table.

While testing this, I came across an interesting bug. And yes, I consider this a bug. On SQL Server 2005 SP3, changes to table column names made via SSMS or via the sp_rename stored procedure (which SSMS uses behind the scenes) are not captured. There was a Microsoft Connect report filed back in 2005 about this that also says the trigger misses changes by sp_autostats, sp_refreshview, and sp_addumpdevice. The Microsoft responder said the issue has been fixed in SQL 2008 and I have verified that (at least for the sp_rename procedure). He also said the request was not submitted in time to get into 2005 SP2. I would have thought though, that it would have made it into 2005 SP 3. That is not the case. In SQL 2005 SP3, the problem still exists. And someone else on the Connect issue says it still isn’t fixed in 2005 SP4. Unfortunately, Microsoft support for 2005 SP 4 ends on 12/13/2011, so don’t expect this to get fixed.

Just an FYI. This could be a big hole in your auditing process.