0

I'd like to audit changes to a table in SQLServer using a trigger and an audit table to do so. End-users connect to the database through an application. If I use SYSTEM_USER to obtain the end user's username, then it returns the name of the application that is connecting to the database, and not the user's unique login name for that application.

What would be a standard approach to capturing the user's unique application login name in this scenario?

Improve this question
4
  • This may be a duplicate of the following question, although if I select context_info in an SQL session, it returns null: stackoverflow.com/questions/22850356/…
    – youcantryreachingme
    Commented Sep 17, 2018 at 4:41
  • Please add the connection string used by the app.
    – McNets
    Commented Sep 17, 2018 at 8:13
  • 3
    If the application connects to SQL Server using a service account, the application code will need to provide the end-user's username via context info or similar other method for auditing.
    – Dan Guzman
    Commented Sep 17, 2018 at 11:35
  • Thanks Dan - from my researching yesterday I was coming to consider context_info as the logical way to do this.
    – youcantryreachingme
    Commented Sep 18, 2018 at 5:18

1 Answer 1

Reset to default
0

Although I noted a potential duplicate question in my comments above, Microsoft has introduced new features to make the passing of (essentially "session") information from a client application to the database easier, with the introduction of new features in SQL Server 2016.

As noted before, CONTEXT_INFO was the solution prior to 2016 - but it is fraught with difficulties.

From 2016 onward, SESSION_CONTEXT has been introduced.

Rather than try and summarise this myself, there is an excellent article describing both methods, their shortcomings and providing SQL examples on their usage. I'm sure there's ample information there to work through a solution to this problem.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.