如何在SQL Server中查询特定用户所属组？xp_logininfo无效求解决

嘿，我懂你用xp_logininfo没成功的糟心感——这个存储过程有时候确实会因为权限、嵌套组或者配置问题掉链子。下面给你几个靠谱的替代方案，以及修正xp_logininfo的小技巧：

先试试修正xp_logininfo的用法 #

如果只是没看到嵌套的AD组，或者权限问题导致失效，可以先试这两步：

• 显示所有嵌套组：默认xp_logininfo只返回直接所属的组，加上第二个参数'all'就能递归显示所有嵌套组：

  Exec master.dbo.xp_logininfo 'DOMAIN\MYNAME', 'all'
  

• 检查权限：这个存储过程需要sysadmin角色的权限才能读取AD信息，确保你执行它的账号有足够权限。

用系统视图查询服务器级别组/角色 #

如果xp_logininfo还是不行，直接查SQL Server的系统视图更可靠，这些视图存储了服务器级别的权限和成员信息：

查询服务器角色 #

比如sysadmin、dbcreator这类内置角色：

SELECT r.name AS 服务器角色名称
FROM sys.server_role_members rm
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.server_principals p ON rm.member_principal_id = p.principal_id
WHERE p.name = 'DOMAIN\MYNAME';

递归查询嵌套的Windows组 #

如果用户是通过多层嵌套的AD组加入SQL Server的，用递归CTE可以把所有层级的组都查出来：

WITH 递归组成员 AS (
    SELECT 
        p.principal_id, 
        p.name AS 成员名称, 
        gp.principal_id AS 组ID,
        gp.name AS 组名称
    FROM sys.server_principals p
    JOIN sys.server_role_members rm ON p.principal_id = rm.member_principal_id
    JOIN sys.server_principals gp ON rm.role_principal_id = gp.principal_id
    WHERE p.name = 'DOMAIN\MYNAME'
    UNION ALL
    SELECT 
        rgm.成员名称,
        rgm.成员名称,
        gp.principal_id,
        gp.name
    FROM 递归组成员 rgm
    JOIN sys.server_role_members rm ON rgm.组ID = rm.member_principal_id
    JOIN sys.server_principals gp ON rm.role_principal_id = gp.principal_id
)
SELECT DISTINCT 组名称 FROM 递归组成员;

查询数据库级别的组/角色 #

如果你需要查看某个特定数据库内的角色（比如db_owner、db_datareader），切换到目标数据库后执行：

USE 你的数据库名称; -- 替换成实际数据库名
SELECT r.name AS 数据库角色名称
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals p ON rm.member_principal_id = p.principal_id
WHERE p.name = 'DOMAIN\MYNAME';

额外提示 #

如果xp_logininfo始终失效，可能是SQL Server的服务账号没有读取AD目录的权限。这时候用系统视图的方法就更稳妥，因为这些视图的数据是SQL Server本地存储的，不需要直接访问AD。

内容的提问来源于stack exchange，提问作者James