If I am auditing user actions and I want to be sure that I can capture the actual login, is there a function I can use in my auditing trigger?
asked
Finding the user's login
There are a couple of built-in functions to give you the login for auditing.
- suser_sname()
- system_user
One thing to keep in mind with these is that a login with impersonate privileges can cause the trigger to record an incorrect login by changing the execution context prior to activating the trigger. Here's a quick demo query:
-- we need a user to spoof
create user bob
-- impersonate bob
execute as user = 'bob'
select suser_sname()
select system_user
-- done impersonating
revert
-- get rid of bob
drop user bob
The system_user function should give you the login of the user executing the query if it is invoked inside a trigger.
