How to Obtain Domain User Name when connecting via SQL Authentication

Question

I have an application which connects to SQL Server 2000 (using a generic SQL Login and SQL Authentication). I would like to implement some logging via triggers to track data changes. I can't use USER_NAME() because that returns the generic account.

I've poked through master..sysprocesses and it doesn't seem to record the username (although it does record the machine name).

Any ideas how, within SQL, to gain access to the username?

(Note: yes, I could pass it in as a variable via the application ... but that would mean I'd have to roll out a new version of the app; I'm trying to do this from within SQL, so I can avoid that, if possible.)

Answer 1

Have you tried SYSTEM_USER?

SELECT SYSTEM_USER

Answer 2

You can't. Simple. SQL Server has no knowledge of the end user with a SQL login at all

The same applies if you use a proxy (web server etc) too: you don't know the end user. We use parameters/stored procs to pass in the username from the web server.

I would suggest that you set CONTEXT_INFO in the client which persists for that connection.