How to get Windows Log-in User Name for a SQL Log in User
To get the user and machine name use this:
SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser
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
- You can get the client ip address and remote PID from querying sessions & connections.
- Use this info to build a TASKLIST command.
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 SON 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 =)