It explains how to implement an audit trail quickly by creating shadow table for each table in database and triggers to log every time when a record is inserted, updated or deleted in the table.

Steps:

1. First run Scripts By Cedric Baelemans of stored proc which takes table name as argument and create shadow table having some additional fields like AuditId, AuditAction, AuditDate, AuditUser and AuditApp in database.

audit trail table sql server Implement Audit Trail Quickly for Microsoft SQL Server

2. Execute stored proc manually for testing on sample table say Customers.

Exec GenerateAudittrail ‘Customers’,'dbo’

It will create shadow table ‘Customers_shadow‘ with insert, update and delete triggers of Customers. When any record is inserted, updated or deleted, trigger is fired and a record is inserted in shadow table with additional information.

audit trail sql server Implement Audit Trail Quickly for Microsoft SQL Server

3. We can do this quickly by running it with SSMS tools Pack. It is the best free Sql Server Management Studio(SSMS)’s add-in having lots of features. Install SSMS Tools Pack if not installed and see this post for more details.

4. Click on SSMS Tools > “Run Custom Script” > Options
5. Give Shortcut ‘Audit‘ and Select ‘Table‘ in ‘Enabled On‘ options and enter following sql script.
Exec GenerateAudittrail ‘|ObjectName|’,'|SchemaName|’
6. Click on Save and OK button.
7. Now, Right click on any table > Select “SSMS Tools” > Run Custom Scripts > Audit
That’s it !!!

audit trail sql server ssms Implement Audit Trail Quickly for Microsoft SQL Server

It will create Audit Table and Triggers automatically. It is very useful and time saver when hundreds of tables in database and we have to implement audit functionality for some tables.

If you are facing issue, see step by step guide to Run Your Custom Script Quickly with SSMS Tools Pack.

Enjoy Auditing !!!

Comments:  2

  • http://profile.yahoo.com/S7VW7R6BQKSGFTSNTHCF7T54HY AleksandrS

    What is wrong with built-in CDC (Change Data Capture, not available in express edition) and CT (change tracking, available in all editions)?
     

  • http://profile.yahoo.com/ICH4TXSUPPKKK4WEQWVNMX6VNM Tridip

    No body like to do audit trail with shadow table. every one like to create single table for all audit trail data.