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

avatar image

Steve Jones - Editor ♦♦
5.1k 79 93 87

(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

avatar image

KenJ
24.5k 3 10 19

(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

avatar image

TimothyAWiseman
15.6k 22 51 38

(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.

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:

x198
x102
x49

asked: Dec 04, 2009 at 09:36 PM

Seen: 3249 times

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

Copyright 2016 Redgate Software. Privacy Policy