从sql server获取windows用户登录名

2021-09-10 00:00:00 sql tsql sql-server

我的数据库配置为使用登录名 sa 的 SQL 服务器身份验证.现在我想知道用户的Windows 登录用户名是什么.SA 将在那里为每个人服务.我能够获得计算机IP地址和计算机名,但我迫切需要用户的Windows登录用户名.我的网络是使用活动目录设置的.

My database is configured to use SQL server authentication with login name sa. Now I would like to know what is the user's Windows login user name. SA will be there for everybody. I was able to get the computer IP address and Computer name, but I desperately need the user's Windows login user name. My network is setup using active directory btw.

set @UserComputerIP = CONVERT(varchar(20),CONNECTIONPROPERTY('client_net_address')) 
set @UserComputerName = CONVERT(varchar(20),HOST_NAME())

任何帮助将不胜感激.(Ps. 不,我无法在 SQL Server 中切换到 Windows 身份验证)

Any help would be appreciated. (Ps. No i cannot switch to Windows Authentication in SQL Server)

推荐答案

如果您不使用 Windows 身份验证,我不确定您是否可以获得域用户名.

I'm not sure if you can obtain the domain user name if you're not using Windows Authentication.

我所知道的最详细的信息是系统表/视图,如果您使用 SQL 登录名连接,这些信息不会显示 NT 用户名.即使服务器处于混合身份验证模式.

The most detailed info that I know of are the system tables/views, and those don't show the NT user name if you connected using a SQL login. Even if the server is in mixed authentication mode.

在 SQL Server 2008 之前的版本上:

On pre-SQL Server 2008:

select nt_username from master.sys.sysprocesses where spid = @@spid

从 SQL Server 2008 开始:

From SQL Server 2008 on:

select nt_user_name from sys.dm_exec_sessions where session_id = @@spid

如果您使用 SQL 身份验证连接,两者都会显示一个空列.

Both will show an empty column if you connected using SQL authentication.

相关文章