Home Forums Article Discussions Article Discussions by Author Discuss content posted by David Balthrop Field Level Auditing using Change Data Capture - Part 1 RE: Field Level Auditing using Change Data Capture - Part 1

  • November 1, 2011 at 11:25 am

    #1401744

    Martin Nyborg (11/1/2011)

    In "normal" web application it is not the logged in user that access the database, but a single trusted nt user or a sql user in the connecting string.

    So where is the username coming from?

    The SYSTEM_USER function returns the context of the user who is running the transaction. This is what I've used in the past to track who has made a change in a Trigger.

    Your application (ssms, web app, etc) is connecting using a SQL or domain account in it's connection string. This value is listed under the changed record.

    So your web app might show ModifiedBy: WebApplicationLogin and you in SSMS might show ModifiedBy: domain\MNyborg.

    With the application using a single account and using no impersonation, it's an extremely limited auditing feature, but in my experience it's nice to know if the application made a change or if a specific developer made a change. It would be amazing to get the specific user of the application who made the change, but my web development team doesn't want to deal with it.