How to know the user doing a delete action on an audit table, when using a shared login?

Question

Background information:

• I'm creating a collection of audit tables to keep track of updates and deletes to a set of data tables for my app.
• Audit records are created via triggers.
• DML in my app's database will generally come from a login that a service uses to get into the database. Because of this, I think the result from SYSTEM_USER will always be the same when called in a trigger.
• My app does not store user data currently, though a string UserId is given to it each time DML is to done (done exclusively in stored procedures).

The problem I ran into is that when a user deletes a record, I want to know who did it. Because it will done by the same login, I don't want to see that all action were done by service, I want to see which user did it. This isn't an issue on an update, because we have ModifiedBy columns that will be updated via a sent in UserId on updates.

The question is: Is there a way to set the SYSTEM_USER or otherwise get the user information into the trigger when a delete is run?

The "best" idea I have right now, though I'm not sure if it is a good idea yet, is that in the service I check to see if the current UserId is in the database as a user, and if not create a user object for them. Then run stored procedures with EXECUTE AS User = @UserId. Then when DML is done in the stored procedure and the trigger fires, SYSTEM_USER should return the user from the EXECUTE AS.

Answer 1

While using EXECUTE AS User = @UserId may be your best option (depending on other issues), here is an alternative approach:

In you stored procedures, or at anytime in your SQL session before you do the DELETE execute the following command:

SET CONTEXT_INFO @UserId

Then in your Trigger you can retrieve this value with

SELECT @var = CAST(CAST(CONTEXT_INFO() As Varbinary(4)) As Int)

This has some disadvantages, the most important of which is that you cannot readily use CONTEXT_INFO for more than one thing at a time.

Answer 2

Depending on how you change the user context from the individual login to the service login, you might find that ORIGINAL_LOGIN() is helpful.

http://technet.microsoft.com/en-us/library/ms189492.aspx

"This function can be useful in auditing the identity of the original connecting context. Whereas functions such as SESSION_USER and CURRENT_USER return the current executing context, ORIGINAL_LOGIN returns the identity of the login that first connected to the instance of SQL Server in that session. "

Answer 3

You could also try adding Host_Name to your tables. I have found in situations where I have a shared login I can usually track down an individual by their machine name since 95% of the time a person is working from their own machine. It doesn't always work, but it can be a useful secondary piece of information.

SELECT host_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID

Unfortunately this won't work if you are working with a web app where the host is always going to be the web application itself but it might be worth trying.