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.2k 79 94 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

25k 3 13 20

(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

15.6k 22 55 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



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: 3835 times

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

Copyright 2018 Redgate Software. Privacy Policy