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)?