How to implement an Audit Log in Sql Server

Question

We have an (asp.net) application that connects to a SQL Server 2008 R2 database using login credentials specifically setup for this application.

Our application access/modifies/deletes records via stored procedures, to which we pass the username of the user performing the action as a parameter.

We need to be able to keep an audit log of all updates and deletes on certain tables. The solution also needs to be as minimal as possible and not require any developer intervention.

The easiest way I can find, is to put a trigger on the table (A) that copies the 'old' data into a history table (A_History) The problem is that we need to be able to tell who performed the action, for an update this is easy we just look at username of the newly inserted record. But for a delete we do not have access to the username parameter sent to the stored procedure.

Is there any way to implement this without using things like CONTEXT_INFO(), because this would need to be added to every stored procedure and every call, which is bound to be forgotten.

Answer 1

SQL Change Tracking may be helpful to you.

Now, you should probably do a few other things:

1. Avoid physically deleting your records.
2. Add an active/deleted column.
3. Make sure your tables have ModifiedByUserID and CreatedByUserID columns.
4. When you update, to mark it deleted or otherwise, update the ModifiedByUserID column with the caller's user id.

Implement all that and SQL will keep track of every version of those records... who created them... who modified them... who marked them as deleted.