Azure环境下输出AD组成员的T-SQL查询编写求助

SQL Server内置系统视图不会存储AD组内部的成员信息，需要借助扩展存储过程xp_logininfo读取AD域数据，和原有查询关联即可实现需求。

前置要求 #

• 确保SQL Server服务运行账号拥有AD域的读取权限
• 启用xp_logininfo扩展存储过程（生产环境启用前请评估安全风险），启用代码如下：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'xp_logininfo', 1;
RECONFIGURE;

修改后完整查询代码 #

SELECT
    ad_members.account_name AS AD组成员账户名,
    ad_members.type AS 成员类型,
    ad_members.permission_path AS 所属AD组路径,
    db_name() AS 所属数据库,
    ad_groups.type_desc AS 主体类型描述
FROM 
    -- 原查询逻辑筛选符合条件的AD组
    (
        SELECT DISTINCT dp.name AS ad_group_name, dp.type_desc
        FROM sys.database_role_members drm
        LEFT JOIN sys.database_principals dp ON dp.principal_id = drm.member_principal_id
        LEFT JOIN sys.database_principals dpr ON dpr.principal_id = drm.role_principal_id
        WHERE dp.type_desc <> 'SQL_USER'
            AND dpr.name <> 'db_datareader'
            AND dp.type IN ('G', 'U') -- 筛选Windows组/用户，排除其他无关主体
    ) ad_groups
CROSS APPLY
    -- 调用扩展存储过程拉取对应AD组的所有成员
    xp_logininfo(ad_groups.ad_group_name, 'members') ad_members

注意事项 #

• 上述查询仅返回AD组的直接成员，如果存在嵌套AD组（组中包含其他组），需要额外添加递归逻辑才能拉取到所有层级的成员
• 仅返回SQL Server实例层面可识别的AD成员，如果AD成员未在实例中配置任何登录权限，可能无法被xp_logininfo捕获
• 该方案仅适用于加入了AD域的SQL Server实例，非域环境无法生效

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