4

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)

Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
Maverick1415
  • 113
  • 1
  • 12
  • 4
    Just to make sure you know: If users are SA, they can wreak extreme havok not only on your whole database instance, but probably the *server* itself. If they are logged in as a SQL Server user, they are *not* logged in as a Windows user; no Windows credentials are passed at all; so you simply can't get that information directly. – Andrew Barber Sep 02 '14 at 18:23
  • 2
    **Don't use SA!** Make another administrator login that you only use for rare needs-admin things, set up everyone to use Windows Authentication, and put the sa password away in a safety deposit box somewhere. – Joel Coehoorn Sep 02 '14 at 20:04
  • Why would you imagine you would be able to get the Windows login in this scenario? I would reconsider given SA access to your entire user base. – Martin Jackson Sep 02 '14 at 20:05

2 Answers2

4

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.

Luc
  • 320
  • 4
  • 11
  • Results came back empty. Its probably not possible. – Maverick1415 Sep 02 '14 at 20:22
  • Yes, I tried that too on my server, and that column is only available for connections with Windows Authentication. If you're using a client application, perhaps you can code something on it, to get the username and then pass it to the server? – Luc Sep 03 '14 at 00:43
1

did you try Below command will give you NT ID

SELECT CONVERT(varchar(20),suser_sname())
Hiten004
  • 2,425
  • 1
  • 22
  • 34
  • you should use trusted connnection string in your database connection. – Hiten004 Sep 02 '14 at 17:35
  • This query returns both domain and user name when connected with a windows user to a mixed security mode SQL Server 2017. It was exactly what I needed, thanks! – Culme Aug 06 '19 at 13:39