如何找出访问SQL服务器的用户名和机器名 [英] How to find out user name and machine name to access to SQL server

问题描述

我的工作公司有一个 MSSQL 服务器 2005.我有两个关于查找当前日志用户和发送警告消息的方法的问题:

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:

第一个问题是是否有任何 T-SQL 或 SP 可用于查找当前登录用户名和机器名.如果用户使用SQL server sa名远程访问SQL server，有什么办法可以查到那个用户的windows名(登录windows的名字)?

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

我的下一个问题是，如果我能得到用户名或id，有没有办法发出警告信息，例如当前SQL服务器正在清理或备份，此时请不要登录".我想这可能很困难.我可能需要向用户发送电子邮件.

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.

SQL 服务器只能在公司内部访问.SQL服务器有一个用户列表作为登录用户:windows用户、SQL用户和sa.

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.

推荐答案

SELECT SUSER_SNAME(), HOST_NAME()

如果连接是sa"(或任何其他 SQL 登录)，则您找不到域/Windows 用户名.SQL Server 只知道它是sa"或那个 SQL 登录名.

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.

HOST_NAME 也可能不可靠，它可以在连接字符串(应用程序名称")中设置.或者它可能是模糊的，例如默认为 Access、Excel 等的Microsoft Office"

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

您可以通过 中的 client_net_address 进行回溯sys.dm_exec_connections 并将 MAC 地址与 IP 匹配并找出谁登录...

You could backtrack via client_net_address in sys.dm_exec_connections and match MAC address to IP and find out who is logged on...

这篇关于如何找出访问SQL服务器的用户名和机器名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！