SQL Server中查询用户未关联顶级角色的角色与组（支持角色递归层级）

嗨，我来帮你搞定这个需求！要找出用户拥有的但不属于任何其可访问的顶级角色层级的角色和组，我们可以通过递归CTE处理角色的父子层级关系，再分步梳理权限并过滤掉属于顶级角色链的部分。以下是完整的解决方案：

解决思路 #

我们分三步实现目标：

• 用递归CTE遍历用户可访问的所有角色（包括父角色继承的子角色），同时标记每个角色所属的顶级角色（即角色链的根节点，且toplevel=1）。
• 整合用户的所有权限：直接分配的角色、直接分配的组、通过角色继承的组。
• 最终过滤掉所有属于顶级角色层级的权限，剩下的就是我们需要的结果。

完整SQL查询代码 #

WITH UserRoleHierarchy AS (
    -- 锚点成员：获取用户直接拥有的角色，标记顶级角色ID
    SELECT 
        ur.uid,
        ur.roleid,
        r.rolename,
        r.toplevel,
        -- 若角色本身是顶级，顶级ID为自身；否则后续递归补全
        CASE WHEN r.toplevel = 1 THEN ur.roleid ELSE NULL END AS top_level_roleid
    FROM userrole ur
    JOIN roles r ON ur.roleid = r.roleid
    WHERE ur.uid = 1 -- 可替换为目标用户ID或参数
    
    UNION ALL
    
    -- 递归成员：遍历子角色，继承父角色的顶级角色ID
    SELECT 
        urh.uid,
        rr.childid AS roleid,
        r_child.rolename,
        r_child.toplevel,
        urh.top_level_roleid -- 子角色继承父角色的顶级标记
    FROM UserRoleHierarchy urh
    JOIN rolerole rr ON urh.roleid = rr.partentid -- 注意表字段`partentid`可能是`parentid`的笔误
    JOIN roles r_child ON rr.childid = r_child.roleid
)
, AllUserPermissions AS (
    -- 1. 用户直接拥有的所有角色（含递归继承的）
    SELECT 
        uid,
        roleid,
        rolename,
        CAST(NULL AS INT) AS gid,
        CAST(NULL AS VARCHAR(50)) AS groupname,
        top_level_roleid
    FROM UserRoleHierarchy
    
    UNION ALL
    
    -- 2. 用户通过角色拥有的所有组（含递归角色的组）
    SELECT 
        urh.uid,
        urh.roleid,
        urh.rolename,
        rg.gid,
        g.groupname,
        urh.top_level_roleid
    FROM UserRoleHierarchy urh
    JOIN rolegroup rg ON urh.roleid = rg.roleid
    JOIN groups g ON rg.gid = g.gid
    
    UNION ALL
    
    -- 3. 用户直接分配的所有组
    SELECT 
        ug.uid,
        CAST(NULL AS INT) AS roleid,
        CAST(NULL AS VARCHAR(50)) AS rolename,
        g.gid,
        g.groupname,
        NULL AS top_level_roleid -- 直接组暂不标记顶级关联，后续判断
    FROM usergroup ug
    JOIN groups g ON ug.gid = g.gid
    WHERE ug.uid = 1 -- 可替换为目标用户ID或参数
)
-- 最终查询：排除所有属于顶级角色层级的权限
SELECT DISTINCT
    a.uid,
    u.name,
    a.roleid,
    a.rolename,
    a.gid,
    a.groupname
FROM AllUserPermissions a
JOIN users u ON a.uid = u.uid
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM UserRoleHierarchy urh_top
        WHERE 
            urh_top.uid = a.uid
            AND urh_top.top_level_roleid IS NOT NULL
            AND (
                -- 排除属于顶级角色链的角色
                (a.roleid IS NOT NULL AND urh_top.roleid = a.roleid)
                -- 排除被顶级角色链中角色覆盖的组
                OR (a.gid IS NOT NULL AND EXISTS (
                    SELECT 1
                    FROM rolegroup rg_top
                    WHERE rg_top.roleid = urh_top.roleid
                    AND rg_top.gid = a.gid
                ))
            )
    )
ORDER BY a.uid, a.roleid, a.gid
-- 若角色层级超过100，添加此选项取消递归限制：OPTION (MAXRECURSION 0);

代码解释 #

1. UserRoleHierarchy 递归CTE

   • 锚点部分先获取用户直接拥有的角色，标记每个角色的顶级角色ID（如果角色本身是toplevel=1，则用自身ID）。
   • 递归部分遍历角色的父子关系，让子角色继承父角色的顶级角色ID，确保同一顶级角色链的所有角色都有相同的top_level_roleid。

2. AllUserPermissions CTE
   整合了用户的三类权限：直接角色、通过角色继承的组、直接分配的组，为后续过滤做准备。

3. 最终过滤逻辑
   用NOT EXISTS排除两类内容：

   • 属于顶级角色链的角色；
   • 被顶级角色链中任意角色覆盖的组。
     用DISTINCT去重，避免重复的权限项。

示例验证（Alice，uid=1） #

运行查询后会得到符合预期的结果：

Alice的Role C不属于顶级角色Role A的链，直接分配的group 3也未被顶级角色覆盖，因此被保留；其余权限因属于Role A的顶级链而被排除。

注意事项 #

• 你的rolerole表字段partentid疑似parentid的笔误，若实际表名不同请自行调整。
• 若查询其他用户，只需修改两处WHERE ug.uid = 1为目标ID，或改为参数（如@userid）。
• 若角色层级超过100，需在查询末尾添加OPTION (MAXRECURSION 0)取消递归深度限制。

内容来源于stack exchange