Get User for Audit Entries 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)?
Depending on how the application is setup, one (maybe the only?) way to pass data at the connection level would be to utilize the ApplicationName portion of the connection string.

You could have the application set this value based on the logged in user, making it accessible via APP_NAME() on the database side.

Example:

Server=myServer;User Id=myUsername;Password=Pass;ApplicationName="MyApp - User LowlyDBA"