Sql-server – Finding The Actual Application User ID In The Transaction Logs

sql server

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?

Best Answer

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.

Related Question