Get User for Audit Entries

by Vaccano   Last Updated September 11, 2019 17:06 PM

I am trying to construct history entries for my application tables. (Basically a "who changed what and when" set of tables.)

My problem is that I have a lot of users. Well over a thousand. My users use UI applications that then call services. The users authenticate to those services using OAuth2 and Open Id Connect (OIDC). So I can't use "Integrated Security" to access SQL Server. Instead I access SQL Server using a service account.

This means a call to SUSER_NAME() will return the service account instead of the user.

I have thousands of SQL queries. It will be A LOT of work to go change each one to add a user parameter.

I am really hoping there is some way I can pass the real user to SQL Server. I know this does not work, but an example would be something similar to adding it to a header in a call.

Is there some way to get the user to SQL Server besides the logged in user?

Or, failing that, is there some way to get SQL Server to work with access tokens (OAuth and OIDC)?

Tags : sql-server audit

Related Questions

Updated August 19, 2015 19:02 PM

Updated September 20, 2017 14:06 PM

Updated April 23, 2016 08:02 AM

Updated January 22, 2018 18:06 PM

Updated April 27, 2016 09:02 AM