AutoAudit

Many SQL Server applications benefit from tracking when and how users insert, update, and delete data. However, building audit tracking into a SQL Server application can be difficult and time consuming. My usual approach is to create triggers on each table in which a user might manipulate data, but that can be arduous work even with small databases, and it can be very difficult to maintain when the tables might be altered over time. As an alternative, you might figure out a way to dynamically build audit triggers, but performance will be disappointing.

Paul Nielsen, a long-time SQL Server MVP and the author of SQL Server Bible (Wiley), knew that there had to be an easier way to create audit triggers. He built a set of T-SQL scripts, called AutoAudit, to dynamically create audit trail triggers that are perfectly tailored to each of their referencing tables. And if the table schema changes, you can simply rerun the T-SQL code to replace the obsolete audit triggers. So how does AutoAudit work?

AutoAudit is designed to run on SQL Server 2008 and SQL Server 2005, and it’s made up of a set of scripts that track all INSERT, UPDATE, and DELETE activity on the audited tables in which it’s implemented. Thus, for an INSERT operation, AutoAudit would create an auditing trigger that shows who inserted the record, when, and from which workstation and application. Similarly, an UPDATE operation will show details of both the before and after image of the records that were updated, and a DELETE operation would show an audit record of who deleted one or more records, when it occurred, and from which workstation and application the DELETE operation was initiated.

AutoAudit includes four essential stored procedures:

• AutoAudit: The core stored procedure in the toolkit, it’s invoked using the syntax of autoaudit ‘schema_name’,’table_name’. AutoAudit first creates a table called dbo.audit if it doesn’t already exist. This is where audit records are stored. It creates a view to reconstruct deleted rows and a user-defined function (UDF) to visualize row history for the audited table. It adds a few important columns to the audited table, including Created, Modified, and RowVersion, for tracking and auditing. In addition, it creates a schema trigger, also known as a DDL trigger, so that if the audited table is ever changed, AutoAudit will be reinvoked to create a new and updated version of the auditing trigger.
• AutoAuditDrop: Drops the DML triggers on the named audited table and schema.
• AutoAuditAll and AuditDropAll: Runs AutoAudit or AutoAuditDrop, respectively, for all the tables in the database, excluding dbo.audit.

AutoAudit doesn’t audit changes to atypical data types, such as text and ntext, image, geography, xml, binary and varbinary, and timestamp. It’s geared toward tables with a single-column primary key, although Paul’s blog shows him helping users implement the toolkit with other configurations of primary keys, such as multicolumn primary keys.

AutoAudit 2, which should be available by the time this article goes to print, will include several new features such as the StrictUserContext feature, which tracks which user performed the DML changes. This feature is especially useful for recording audit details where an application has internal user controls and permissions, instead of SQL Server-based controls and permissions.

The latest release, at the time of this writing, is available at autoaudit.codeplex.com. Also, there’s a really good discussion on Paul’s initial AutoAudit blog post, which I think is worth reading.