How to get Windows Log-in User Name for a SQL Log in User

Question

By using sp_who2 I can get a list of current log-in users and machine names. Some users are using SQL Server log-in user name such as sa. Is there any way I can get the windows log-in user name for the SQL log-in users?

If there is no way to get Windows log-in users from the machine names, can I use WMI class or C# to get find out the Windows log-in user names by their machine names?

My SQL server is Microsoft SQL Server 2005 and Windows is Server 2003.

Duumvir

+1 Well, I wish I knew about this... But Alas, my company has moved onto Windows Authentication Mode only environment; But I would like to find out if this is possible. – Dextral

For the WMI portion, #528497 – Cray

Answer 1

There is no link between a SQL login and the NT username.

You asked similar here: How to find out user name and machine name to access to SQL server

The WMI approach will be ambiguous if more than 1 user is logged into the client PC (eg service accounts, remote user via mstsc etc). Any approach like this will require admin rights on the client PC too for the account used.

I doubt you can do it in real time.

All you can do is record the client_net_address in sys.dm_exec_connections and backtrack from there, perhaps via WMI but not from SQL Server itself.

Do you need the username though? Or just the client PC so you can change the app connection string?

You final solution is to change the sa password and see who calls, if you only have relatively few SQL connections

Quilting

Answer 2

To get the user and machine name use this:

SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser

Baksheesh

This will not give the NTID which the OP is asking. – Gamophyllous

This does give the windows user name (Domain\UserName) that OP is asking for. It doesn't give an NT SID. – Cray

No, this can only give the NT username iff the user is logged into the SQL Server using Windows Authentication. Reading the OP again will show that they wanted somehow to get the NT name for people connected using SQL logins. – Drogin

Answer 3

1. You can get the client ip address and remote PID from querying sessions & connections.
2. Use this info to build a TASKLIST command.

3. Use XP_CMDShell to execute the built command to get the user.

   DECLARE @CMD VARCHAR(500) = 
   (SELECT  TOP 1 'tasklist /S ' + client_net_address + 
   ' /FI "PID eq ' + CONVERT(VARCHAR(MAX),host_process_id) + 
   '" /V /FO LIST /U DOMAIN\Admin /P password' 
   FROM sys.dm_exec_connections C JOIN sys.dm_exec_sessions S
   ON C.session_id = S.session_id WHERE S.session_id = @@SPID)
   EXEC xp_cmdshell @CMD

You can use it as you please. Either to send a mail to DBA by using it in an ALL SERVER trigger or for Ad-Hoc auditing. Hope this helps =)

Dorsy

Answer 4

I did try the mentioned script but it gave me an error is there any mistake I did?

Error: output The system cannot find the file specified. NULL

Peewit

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review – Whomsoever

Answer 5

I don't know if there is a way to do exactly what you are asking. However, what I've done in the past is use sql profiler and included the "Host Name" in the result columns. The machine names where I work happen to be unique and can be tracked back to a user. If your machine names are unique as well, this may get the result that you need. You can filter on login name = sa to narrow down the results.

Superbomb

Answer 6

I'm shooting in the dark but maybee my thoughts will help you find your answer. From what I can tell there is no direct way to get this. Which IMHO is a good thing. Now a couple thoughts:

If this is a custom application, you could include that information in the Connection string as Application Name perhaps. If this is a server appplication and your using impersonation you will loose ability to pool connections if you do this. On a client app this shouldn't be a problem.

Do your clients only have a single logged in user at any given time? For example a desktop application? You could use WMI as such. If again this is a server and you want to know whose security context its running under you might still be able to get this information. Otherwise you could at the least figure out who launched the process.

SQL Profiler knows the PID of the client process. But I couldn't find where it's stored in SQL. If you can find how you can get the PID (You could just run a trace programatically and store the login event to a table). You can get the launching user using this script.

Tarn

yes. I have to find another way to figure out user name such as a new service from IT to get default user name for a machine and current user. – Duumvir

Answer 7

OK. I tried to use WMI class to get a remote computer's information (WMI class \fullmancinename\roor\cimc2, and object query select * from Win32_ComputerSystem). It works in my local computer but with a remote computer name, you need pass a user name and password with enough security permission to access or read. It is another windows security issue to deal with to get user name from a remote computer.

My try codes are based on the reference C# Read Windows Logon User Name in WMI.

Duumvir