Get Database IDs and not System Administrators

[DCSage]

One of my reports is returning the incorrect data for the user who made the last update. The report is showing the system user (or only the system admins) who made changes, and not the actual user. So if for example, John updated his password, but Jill is the actual system administrator who modified his acocunt, the report returns Jill as the person who made the changes last. As I am going through the stored procedures and triggers for this report, I realize that the following is prevalent throughout: System_user is in all of the statements. How can I change this so that the value of the current logged in user, and not the system admin are captured. Could I use current_user or is there another value that can be used?



Part of the Trigger Example

Insert into commonEvents
(UserId, Login, Description)
VALUES
(@userId, system_user, @description)

 

[Crowley16]

well, there's suser_sname() for the NT user name if you're not using sql server authentication...

--------------------
Procrastinate Now!

 

[DCSage]

Hi. Thank you for replying. I tried a few ways to actually get the userid for this audit trigger. I can't see where else in the statement the updates are being made. i looked online for examples of audit triggers, but none seem to apply to what I am analyzing. Is there a site with comprehensive info on audit triggers?

 

[SQLSister]

IF Jill made the changes, she is the person who should be recorded. Who the user is is irrelelevant if the Admin made the changes.

"NOTHING is more important in a database than integrity." ESquared