http://www.sqlservercentral.com/blogs/steve_jones/2010/05/25/quick-audits-of-schema-changes/

Printed 2014/07/22 11:51PM

Quick Audits of Schema Changes

By Steve Jones, 2010/05/25

I saw someone post a question recently about tracking down who deleted a stored procedure. I suggested the default trace, and linked to a beginner article on it.

However there was another post that had a better way to track things. In SSMS there’s a report that will help you:

auditreport

Right click a database, and drill down as shown above through reports to the “Schema Change History” report and select it. Previously I had issued this:

ALTER TABLE aa ADD bb varchar(20)


And when I ran my report, I found this in the list:



auditreport2



It doesn’t give the code, but it says that I altered the proc today.



I then tested for stored procedure. I created, and then deleted a quick proc, so it only existed on my system for a few minutes. When I ran the schema report again, despite this proc no longer being around, it was there:



auditreport3



This comes off the default trace, so there’s only a limited amount of data kept. If you catch something quick enough, however, you can easily track down some information about it.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.