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.

• Jeremy Pridemore: @RBarryYoung And that mechanism is the subject of the question. My service is coming into my database doing actions for whoever called it, and I have the UserId available. I need to figure out how to record that UserId in the case of a delete.
• RBarryYoung: Fair enough, I should have read your question more thoroughly. I think I have an answer for this, but I may not be able to post it until late tonight.

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.

• Jeremy Pridemore: We decided to not have the information for now. If we decide we must have it, I'll try this one first. Thanks for the idea.

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. "

• Jeremy Pridemore: That's a neat function, thanks for bringing it up. I'm pretty sure that when I have a service running and hitting the database with the same server login every time, then ORIGINAL_LOGIN() will always return the user that the service is using. Does that sound correct to you?
• RLF: Yes, if you are going through the service account to make the connection to the database, then the ORIGINAL_LOGIN() would be the service. If you change context after connecting to the database as yourself, then the ORIGINAL_LOGIN() should be your login.

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.