Tracking User activity log for SQL Server database

Question

I have a database with multiple tables and I want to log the users activity via my MVC 3 web application.

User X updated category HELLO. Name changed from 'HELLO' to 'Hi There' on 24/04/2011       
User Y deleted vehicle Test on 24/04/2011.
User Z updated vehicle Bla. Name changed from 'Blu' to 'Bla' on 24/04/2011.
User Z updated vehicle Bla. Wheels changed from 'WheelsX' to 'WheelsY' on 24/04/2011.
User Z updated vehicle Bla. BuildProgress changed from '20' to '50' on 24/04/2011

My initial idea is to have on all of my actions that have database crud, to add a couple lines of code that would enter those strings in a table.

Is there a better way of checking which table and column has been modified than to check every column one by one with if statements (first I select the current values, then check each of them with the value of the textbox) I did that for another ASPX web app and it was painful.

Now that I'm using MVC and ADO.NET Entity Data Model I'm wondering if a faster way to find the columns that were changed and build a log like the one above.

Answer 1

You can also accomplish this by putting your database into full recovery mode and then reading the transaction log.

When database is in a full recovery mode then sql server logs all Update, insert and delete (and others such as create, alter, drop..) statements into it's transaction log.

So, using this approach you dont need to make any additinal changes to your application or your database structure.

But you will need 3rd party sql transaction log reader. Red gate has a free solution for sql server 2000 only. If your server is 2005 or higher you would probably want to go with ApexSQL Log

Also, this approach will not be able to audit select statements but it's definately the easiest to implement if you dont really need to audit select queries.