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.