Get windows user login name from sql server

Question

My database is configured to use SQL server authentication with login name sa. Now I would like to know what is the user's Windows login user name. SA will be there for everybody. I was able to get the computer IP address and Computer name, but I desperately need the user's Windows login user name. My network is setup using active directory btw.

set @UserComputerIP = CONVERT(varchar(20),CONNECTIONPROPERTY('client_net_address')) 
set @UserComputerName = CONVERT(varchar(20),HOST_NAME())

Any help would be appreciated. (Ps. No i cannot switch to Windows Authentication in SQL Server)

Answer 1

I'm not sure if you can obtain the domain user name if you're not using Windows Authentication.

The most detailed info that I know of are the system tables/views, and those don't show the NT user name if you connected using a SQL login. Even if the server is in mixed authentication mode.

On pre-SQL Server 2008:

select nt_username from master.sys.sysprocesses where spid = @@spid

From SQL Server 2008 on:

select nt_user_name from sys.dm_exec_sessions where session_id = @@spid

Both will show an empty column if you connected using SQL authentication.