How can I audit a web application specifically database changes, when the application is connecting to the database as a system account?
I would like to use a simple database trigger to write to an audit table but I am unsure of how I would be able to add the user that is logged on instead of the account that is actually doing the work.
I have read a few articles on identity flow but I do not fully understand the concept.
I have the same issue. None of the example I have seen on the net save user information. It seems people are more concerned about the database user instead of the web application user. I mean really WTF!!!!! I dont give two hoots about that user because that will always be sa or whatever, all I am concerned and what the application business user is concerned is that which bloody WEBSITE USER did that db change?? triggers on db DOES NOT provide that information. How did you get around this problem?
If you're on MSSQL 2008, you should look into CDC (Change Data Capture):
As exeshu mentions, tiggers/CDC won't audit who your web-app user was unless you have some audit columns on all your tables (createdBy, lastModifiedBy etc - populated on every insert/update), so maybe look into something like that.