I recently needed to implement a lightweight, simple audit trail in SQL Server 2005. After googling and finding many, many ways, I settled on Richard's recent blog post over at GeekDojo titled "Super easy SQL Server 2005 Database Schema change auditing".
Richard has posted a short, helpful script to audit data definition (CREATE, DROP, ALTER) SQL statements to a central table using a simple trigger and the XML data type. This means I can keep an audit trail of schema changes, along with all the information available (user name, date & time, SQL statement, etc.)
I'm looking forward to working with this. Any advice for pulling data out of the XML data type?
Tags: sql server, audit, schema