Finding the user's login

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?

more ▼

asked Dec 04, 2009 at 09:36 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Dec 05, 2009 at 01:34 AM

KenJ gravatar image

20.3k 1 4 12

Exactly. Use suser_sname().
Dec 05, 2009 at 04:02 AM David Wimbush
(comments are locked)
10|1200 characters needed characters left

The system_user function should give you the login of the user executing the query if it is invoked inside a trigger.

more ▼

answered Dec 04, 2009 at 09:50 PM

TimothyAWiseman gravatar image

15.6k 21 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 04, 2009 at 09:36 PM

Seen: 1843 times

Last Updated: Dec 04, 2009 at 09:36 PM