0

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.

Adonis L
  • 1,679
  • 4
  • 20
  • 23

1 Answers1

0

If you're on MSSQL 2008, you should look into CDC (Change Data Capture):

http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

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.

JonoW
  • 14,029
  • 3
  • 33
  • 31