2

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

  • Have you tried using ORIGINAL_LOGIN() function ? – Kin Shah Sep 11 at 17:08
  • @KinShah - The problem is that my users do not have a login on the system. It is always just the system account. To the actual user that is causing the action never logs into the SQL Server. – Vaccano Sep 11 at 17:19
  • @LowlyDBA - I like the way that idea is going. Most of my developers use connection pooling. So I will need a way change the Application Name for an existing connection. I will look into the possibility of that. Thank you for the suggestion! – Vaccano Sep 11 at 17:46
2

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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