- 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_USERwill always be the same when called in a trigger.
- My app does not store user data currently, though a string
UserIdis 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