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.