如何获取 SQL 登录用户的 Windows 登录用户名 [英] How to get Windows Log-in User Name for a SQL Log in User

问题描述

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

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?

如果无法从机器名中获取Windows登录用户，是否可以使用WMI类或C#通过机器名来获取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?

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

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

推荐答案

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

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

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

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

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

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.

您所能做的就是在 sys.dm_exec_connections 中记录 client_net_address 并从那里回溯，可能通过 WMI 而不是从 SQL Server 本身.

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.

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

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

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

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

这篇关于如何获取 SQL 登录用户的 Windows 登录用户名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！