如何避免Active Directory组递归CTE中的无限循环

针对单表递归CTE遇到组循环引用导致无限递归的问题，可通过以下几种方式修改查询：

方法1：跟踪已访问的组路径（推荐） #

在递归CTE中新增一列存储当前递归路径，每次递归时检查即将访问的组是否已在路径中，避免重复进入循环。

修改后的查询代码：

WITH GroupMembers AS (
    SELECT 
        CN_GroupName, 
        CN_MemberName, 
        Member_Is_Group, 
        GroupDomain, GroupParent, GroupName, GroupClass, GroupGUID,
        UserDomain, UserParent, UserName, UserClass, UserGUID,
        1 AS [level],
        -- 初始化路径，用分隔符区分组名
        CAST(CN_GroupName AS nvarchar(max)) AS GroupPath
    FROM dbo.GROUP_MEMBER
), ExpandedGroupMembers AS (
    SELECT 
        CN_GroupName, 
        CN_MemberName, 
        CN_GroupName AS DirectGroup,
        Member_Is_Group, 
        GroupDomain, GroupParent, GroupName, GroupClass, GroupGUID,
        UserDomain, UserParent, UserName, UserClass, UserGUID,
        [level] + 1 AS Lev,
        GroupPath
    FROM GroupMembers

    UNION ALL
    
    SELECT 
        gm1.CN_GroupName, 
        gm2.CN_MemberName, 
        gm1.CN_MemberName AS DirectGroup, 
        gm2.Member_Is_Group, 
        gm2.GroupDomain, gm2.GroupParent, gm2.GroupName, gm2.GroupClass, gm2.GroupGUID,
        gm2.UserDomain, gm2.UserParent, gm2.UserName, gm2.UserClass, gm2.UserGUID,
        gm1.Lev,
        -- 拼接新路径
        CAST(gm1.GroupPath + '|' + gm2.CN_GroupName AS nvarchar(max)) AS GroupPath
    FROM ExpandedGroupMembers AS gm1
    INNER JOIN GroupMembers AS gm2 ON gm1.CN_MemberName = gm2.CN_GroupName
    WHERE gm1.Member_Is_Group = 'Y'
      -- 检查当前组是否已在路径中，避免循环
      AND CHARINDEX('|' + gm2.CN_GroupName + '|', '|' + gm1.GroupPath + '|') = 0
)
SELECT Lev, *
FROM ExpandedGroupMembers as FinalResult
ORDER BY CN_GroupName

说明：

• 用GroupPath记录从起始组到当前组的完整路径，分隔符|避免组名包含特殊字符时的误判
• 递归时通过CHARINDEX检查目标组是否已在路径中，若存在则跳过，彻底阻断循环

方法2：限制递归深度 #

如果能预估AD组的最大层级，可通过MAXRECURSION选项强制限制递归次数，防止查询因无限递归被取消。

修改后的查询末尾添加选项：

SELECT Lev, *
FROM ExpandedGroupMembers as FinalResult
ORDER BY CN_GroupName
OPTION (MAXRECURSION 100) -- 设置最大递归次数，根据实际情况调整

说明：

• 该方法只是临时避免崩溃，无法彻底解决循环问题，若实际层级超过设置值，会截断部分结果
• 默认递归上限是100，设置为0表示无限制（不推荐，仍会触发循环）

方法3：提前排除循环组 #

先查询出所有存在循环引用的组，在递归CTE的初始查询中排除这些组，或对其做特殊处理。

查询循环组的示例：

WITH RecursiveGroups AS (
    SELECT 
        CN_GroupName, 
        CN_MemberName, 
        CAST(CN_GroupName AS nvarchar(max)) AS Path,
        1 AS Level
    FROM dbo.GROUP_MEMBER
    WHERE Member_Is_Group = 'Y'
    UNION ALL
    SELECT 
        rg.CN_GroupName, 
        gm.CN_MemberName, 
        CAST(rg.Path + '|' + gm.CN_GroupName AS nvarchar(max)) AS Path,
        rg.Level + 1 AS Level
    FROM RecursiveGroups rg
    JOIN dbo.GROUP_MEMBER gm ON rg.CN_MemberName = gm.CN_GroupName
    WHERE gm.Member_Is_Group = 'Y'
      AND CHARINDEX('|' + gm.CN_GroupName + '|', '|' + rg.Path + '|') = 0
)
-- 找出所有参与循环的组
SELECT DISTINCT CN_GroupName AS CyclicGroup
FROM RecursiveGroups rg
JOIN dbo.GROUP_MEMBER gm ON rg.CN_MemberName = gm.CN_GroupName
WHERE gm.CN_GroupName = rg.CN_GroupName

之后在原递归CTE的初始查询中添加WHERE CN_GroupName NOT IN (SELECT CyclicGroup FROM 上述查询结果)即可排除循环组。

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