x

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 '09 at 09:36 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 76 79 82

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

2 answers: sort oldest

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 '09 at 01:34 AM

KenJ gravatar image

KenJ
19.1k 1 3 11

Exactly. Use suser_sname().
Dec 05 '09 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 '09 at 09:50 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x158
x81
x42

asked: Dec 04 '09 at 09:36 PM

Seen: 1253 times

Last Updated: Dec 04 '09 at 09:36 PM