如何获取 SQL 登录用户的 Windows 登录用户名

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

提问by David.Chu.ca

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?

通过使用 sp_who2，我可以获得当前登录用户和机器名称的列表。一些用户正在使用 SQL Server 登录用户名，例如 sa。有什么办法可以获得SQL登录用户的Windows登录用户名吗？

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?

如果无法从机器名中获取Windows登录用户，是否可以使用WMI类或C#通过机器名查找Windows登录用户名？

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

我的 SQL 服务器是 Microsoft SQL Server 2005，Windows 是 Server 2003。

采纳答案by gbn

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

SQL 登录名和 NT 用户名之间没有链接。

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

你在这里问了类似的问题：如何找出用户名和机器名来访问 SQL 服务器

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.

如果超过 1 个用户登录到客户端 PC（例如服务帐户、通过 mstsc 的远程用户等），WMI 方法将是不明确的。对于使用的帐户，任何此类方法也需要在客户端 PC 上具有管理员权限。

I doubt you can do it in real time.

我怀疑你能实时做到这一点。

All you can do is record the client_net_addressin sys.dm_exec_connectionsand backtrack from there, perhaps via WMI but not from SQL Server itself.

所有你能做的就是记录client_net_address中sys.dm_exec_connections，并从原路返回那里，也许通过WMI但不能从SQL Server本身。

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

你需要用户名吗？或者只是客户端 PC 以便您可以更改应用程序连接字符串？

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

您最终的解决方案是更改sa密码并查看谁调用，如果您只有相对较少的SQL连接

回答by JohnM

To get the user and machine name use this:

要获取用户名和机器名，请使用以下命令：

SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser

回答by Deepak A S

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

1. 您可以从查询会话和连接中获取客户端 IP 地址和远程 PID。
2. 使用此信息构建 TASKLIST 命令。

3. 使用 XP_CMDShell 执行构建的命令来获取用户。

   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 =)

你可以随意使用它。通过在 ALL SERVER 触发器中使用邮件或用于 Ad-Hoc 审计来向 DBA 发送邮件。希望这会有所帮助 =)

回答by Paul G

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.

我不知道是否有办法完全按照您的要求进行操作。但是，我过去所做的是使用 sql profiler 并在结果列中包含“主机名”。我工作的机器名称恰好是唯一的，可以追溯到用户。如果您的机器名称也是唯一的，这可能会得到您需要的结果。您可以过滤登录名 = sa 以缩小结果范围。

回答by JoshBerke

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.

您的客户在任何给定时间是否只有一个登录用户？例如桌面应用程序？您可以像这样使用WMI。如果这又是一个服务器，并且您想知道它在谁的安全上下文下运行，您仍然可以获取此信息。否则你至少可以弄清楚是谁启动了这个过程。

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.

SQL Profiler 知道客户端进程的 PID。但是我找不到它在 SQL 中的存储位置。如果您能找到如何获取 PID（您可以以编程方式运行跟踪并将登录事件存储到表中）。您可以使用此脚本获取启动用户。

回答by David.Chu.ca

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.

好的。我尝试使用 WMI 类来获取远程计算机的信息（WMI 类 \fullmancinename\roor\cimc2，以及对象查询 select * from Win32_ComputerSystem）。它适用于我的本地计算机，但使用远程计算机名称，您需要传递具有足够安全权限的用户名和密码才能访问或读取。从远程计算机获取用户名是另一个需要处理的 Windows 安全问题。

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

我的尝试代码基于参考C# Read Windows Logon User Name in WMI。