Finding The Actual Application User ID In The Transaction Logs

Question

I am trying to make any row in the database auditable and transaction logs seem to capture what changed when. With regard to who made the change, this link explains how to extract the database user ID from trace logs: How to find out who deleted some data SQL Server.

This would be OK for SMSS users since they would all have different database user IDs. But because the web application has a single database user ID in its connection string, every transaction coming from the web application will refer to the same user. I want to be able to find out the application user who made the change, not the database user.

Is there a way that I can pass this actual user ID information as some kind of meta data so that it will be logged as well for each transaction?

Answer 1

I want to be able to find out the application user who made the change, not the database user.

Then you're going to need to fundamentally change the way your application behaves in terms of security and auditing. You'll also need to setup some sort of auditing, either in the application or database.

Is there a way that I can pass this actual user ID information as some kind of meta data so that it will be logged as well for each transaction?

No, the log was not meant to be a developer tool.

Answer 2

I do not think there is a way to figure out the application user from sql server perspective, SQL server only knows who connects to it, and at the database level,the database knows who the database user is, what actions the database user takes.

It is like when you have a key to a door lock, assuming this lock can be opened by multiple keys, the lock knows which key is used, but the lock cannot know who uses the key. But the key should know who (the key holder) uses it.

Your application is the key holder, your connection string is the key, and your database is the lock.