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

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.

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.

StackExchange Database Administrators Q#50103, answer score: 4