如何利用xp_logininfo批量检测AD安全组重复问题？

一、批量提取所有Windows组及其成员 #

先创建存储成员信息的表，通过游标遍历数据库中的所有Windows组，调用xp_logininfo批量获取成员：

-- 创建存储组及成员信息的临时表
CREATE TABLE #GroupMembers (
    GroupName NVARCHAR(128),
    AccountName NVARCHAR(128),
    Type VARCHAR(8),
    Privilege VARCHAR(8),
    MappedLoginName NVARCHAR(128),
    PermissionPath NVARCHAR(128)
)

-- 创建存储所有Windows组的临时表
CREATE TABLE #AllWindowsGroups (
    GroupName NVARCHAR(128)
)

-- 插入数据库中所有Windows组（类型为'G'）
INSERT INTO #AllWindowsGroups (GroupName)
SELECT [name]
FROM [sys].[database_principals]
WHERE [type] = 'G'

-- 声明游标遍历每个组
DECLARE @GroupName NVARCHAR(128)
DECLARE GroupCursor CURSOR FOR
SELECT GroupName FROM #AllWindowsGroups

OPEN GroupCursor
FETCH NEXT FROM GroupCursor INTO @GroupName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 调用xp_logininfo获取组成员，插入临时表
    INSERT INTO #GroupMembers (AccountName, Type, Privilege, MappedLoginName, PermissionPath)
    EXEC xp_logininfo @GroupName, 'members'
    
    -- 更新当前组的GroupName字段
    UPDATE #GroupMembers
    SET GroupName = @GroupName
    WHERE GroupName IS NULL

    FETCH NEXT FROM GroupCursor INTO @GroupName
END

CLOSE GroupCursor
DEALLOCATE GroupCursor

二、识别重复/异常组 #

1. 找出无任何成员的组 #

SELECT g.GroupName
FROM #AllWindowsGroups g
LEFT JOIN #GroupMembers gm ON g.GroupName = gm.GroupName
WHERE gm.GroupName IS NULL

2. 找出成员完全相同的组 #

通过将每个组的成员拼接为唯一字符串，分组后找到成员重复的组：

WITH GroupMemberLists AS (
    SELECT 
        GroupName,
        STRING_AGG(AccountName, ';') WITHIN GROUP (ORDER BY AccountName) AS MemberList
    FROM #GroupMembers
    GROUP BY GroupName
)
SELECT MemberList, STRING_AGG(GroupName, ';') AS DuplicateGroups
FROM GroupMemberLists
GROUP BY MemberList
HAVING COUNT(*) > 1

3. 找出权限相同但成员不同的组 #

先提取每个组的数据库权限，再对比权限集合：

-- 创建存储组权限的临时表
CREATE TABLE #GroupPermissions (
    GroupName NVARCHAR(128),
    Permission NVARCHAR(128)
)

-- 插入所有Windows组的数据库权限
INSERT INTO #GroupPermissions (GroupName, Permission)
SELECT 
    dp.name AS GroupName,
    perm.permission_name
FROM sys.database_principals dp
JOIN sys.database_permissions perm ON dp.principal_id = perm.grantee_principal_id
WHERE dp.type = 'G'

-- 拼接权限字符串，找出权限相同的组
WITH GroupPermissionLists AS (
    SELECT 
        GroupName,
        STRING_AGG(Permission, ';') WITHIN GROUP (ORDER BY Permission) AS PermissionList
    FROM #GroupPermissions
    GROUP BY GroupName
)
SELECT PermissionList, STRING_AGG(GroupName, ';') AS GroupsWithSamePermissions
FROM GroupPermissionLists
GROUP BY PermissionList
HAVING COUNT(*) > 1

三、清理临时表 #

DROP TABLE #GroupMembers
DROP TABLE #AllWindowsGroups
DROP TABLE #GroupPermissions

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