Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
@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. –  Jeremy Pridemore Sep 18 '13 at 4:10
    
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. –  RBarryYoung Sep 18 '13 at 13:21
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
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. –  Jeremy Pridemore Sep 23 '13 at 19:15
add comment

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

share|improve this answer
    
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? –  Jeremy Pridemore Sep 18 '13 at 3:30
    
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. –  RLF Sep 19 '13 at 12:13
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.