SQL 如何找出访问SQL服务器的用户名和机器名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/641515/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find out user name and machine name to access to SQL server
提问by David.Chu.ca
My work company has a MSSQL server 2005. I have two questions about finding out current log user and any way to send out a warning message:
我的工作公司有一个 MSSQL 服务器 2005。我有两个关于查找当前日志用户和发送警告消息的任何方式的问题：
First question is if there is any T-SQL or SP available to find out current login user name and machine name. If the user is using SQL server sa name to remotely access to SQL server, is there any way to find out that user's windows name (the name to log to the windows)?
第一个问题是是否有任何 T-SQL 或 SP 可用于查找当前登录用户名和机器名。如果用户使用SQL server sa name远程访问SQL server，有什么办法可以查到该用户的windows名称（登录windows的名称）？
My next question is that if I can get the user name or id, is there any way to send out a warning message such as "currently SQL server is clean up or backup, please do not log in at this time". I guess it may be difficult. I may have to send an email out to the user.
我的下一个问题是，如果我能得到用户名或id，有没有办法发出警告信息，例如“当前SQL服务器正在清理或备份，此时请不要登录”。我想这可能很困难。我可能必须向用户发送电子邮件。
The SQL server is only accessible in the company. The SQL server has a list of users as login users: windows users, SQL users and sa.
SQL 服务器只能在公司内部访问。SQL 服务器有一个用户列表作为登录用户：windows 用户、SQL 用户和 sa。
回答by gbn
SELECT SUSER_SNAME(), HOST_NAME()
If the connection is "sa" (or any other SQL login) then you can't find the domain/windows user name. SQL Server only knows it's "sa" or that SQL login.
如果连接是“sa”（或任何其他 SQL 登录名），那么您将找不到域/Windows 用户名。SQL Server 只知道它是“sa”或那个 SQL 登录名。
HOST_NAME may not be reliable either, it can be set in the connection string ("Application Name"). Or it could be vague eg "Microsoft Office" for by default for Access, Excel etc
HOST_NAME 也可能不可靠，它可以在连接字符串（“应用程序名称”）中设置。或者它可能是模糊的，例如默认情况下 Access、Excel 等的“Microsoft Office”
You could backtrack via client_net_addressin sys.dm_exec_connectionsand match MAC address to IP and find out who is logged on...
您可以通过client_net_addressin回溯sys.dm_exec_connections并将 MAC 地址与 IP 匹配并找出谁登录...
回答by Joakim Backman
An easy way to find out both host and user is
找出主机和用户的一种简单方法是
EXEC sp_who2;
where you get some other information that can be good to know, as if the user is active and so on... It does not resolve the issues that gbn announced.
在那里您可以获得一些其他有用的信息，就好像用户处于活动状态等等......它并没有解决 gbn 宣布的问题。
回答by David.Chu.ca
Thanks for all your suggestions first. I tried all the methods and I think Joakim Backman's method meet my need. Here is summary of what I find out.
首先感谢您的所有建议。我尝试了所有方法，我认为 Joakim Backman 的方法满足我的需要。这是我发现的摘要。
- Query of sys.syslogins only list login information. The accdate does not give the current login user timestamp. I tried to login from another application to my SQL and this query does not list the login.
- SELECT SUSER_SNAME(), HOST_NAME() only list one user on SQL server. For example, I log in in as my name to SQL server. The result of this query only lists my name and machine name. This query does not list current users on the SQL server.
- exec sp_who2 lists information I need. It lists current user name machine name, active status, db name user's access, and command used.
- sys.syslogins 查询只列出登录信息。accdate 不提供当前登录用户的时间戳。我尝试从另一个应用程序登录到我的 SQL，但此查询未列出登录名。
- SELECT SUSER_SNAME(), HOST_NAME() 仅列出 SQL 服务器上的一个用户。例如，我以我的名字登录到 SQL 服务器。此查询的结果仅列出我的姓名和机器名称。此查询不会列出 SQL 服务器上的当前用户。
- exec sp_who2 列出了我需要的信息。它列出了当前用户名机器名、活动状态、数据库名用户的访问权限和使用的命令。
In order to get the information I use in SP, I have to filter and join the information with other tables, such as emails. Here is the codes I use:
为了获得我在 SP 中使用的信息，我必须过滤并将信息与其他表（例如电子邮件）连接起来。这是我使用的代码：
DECLARE @retTable TABLE (
SPID int not null
, Status varchar (255) not null
, Login varchar (255) not null
, HostName varchar (255) not null
, BlkBy varchar(10) not null
, DBName varchar (255) null
, Command varchar (255) not null
, CPUTime int not null
, DiskIO int not null
, LastBatch varchar (255) not null
, ProgramName varchar (255) null
, SPID2 int not null
, REQUESTID INT
)
INSERT INTO @retTable EXEC sp_who2
SELECT Status, Login, HostName, DBName, Command, CPUTime, ProgramName -- *
FROM @retTable
--WHERE Login not like 'sa%' -- if not interested in sa
ORDER BY Login, HostName
