SQL Server 遞迴函數實作使用者角色繼承關係樹查詢

T-SQL 表值函數：查詢角色/使用者繼承鏈 (祖先/子孫)

適用於權限管理系統 (角色嵌套)、ERP BOM 展開、組織架構遍歷等樹狀/圖狀結構查詢。

函數定義: dbo.fn_GetUserInheritanceTree

CREATE FUNCTION dbo.fn_GetUserInheritanceTree
(
    @UserName SYSNAME,      -- 起始使用者/角色名稱
    @Direction BIT          -- 0 = 向下查詢子孫, 1 = 向上查詢祖先
)
RETURNS @Result TABLE
(
    UserID      SYSNAME,    -- principal_id
    UserName    SYSNAME,    -- name
    LevelDepth  INT         -- 階層深度 (1=起始節點)
)
AS
BEGIN
    DECLARE @StartPrincipalID INT = USER_ID(@UserName);
    IF @StartPrincipalID IS NULL RETURN; -- 使用者不存在

    DECLARE @Stack TABLE (PrincipalID INT PRIMARY KEY, LevelDepth INT);
    INSERT @Stack VALUES (@StartPrincipalID, 1);

    WHILE EXISTS (SELECT 1 FROM @Stack)
    BEGIN
        -- 取得當前層級最小 LevelDepth 的節點 (廣度優先) 或任一節點
        DECLARE @CurrentID INT, @CurrentLevel INT;
        SELECT TOP 1 @CurrentID = PrincipalID, @CurrentLevel = LevelDepth
        FROM @Stack ORDER BY LevelDepth; -- BFS 順序

        -- 輸出結果
        INSERT @Result VALUES (@CurrentID, USER_NAME(@CurrentID), @CurrentLevel);

        -- 從堆疊移除
        DELETE FROM @Stack WHERE PrincipalID = @CurrentID;

        -- 尋找下一層節點
        IF @Direction = 1 -- 向上找祖先: 當前是成員，找群組
            INSERT @Stack
            SELECT rm.role_principal_id, @CurrentLevel + 1
            FROM sys.database_role_members rm
            WHERE rm.member_principal_id = @CurrentID
              AND NOT EXISTS (SELECT 1 FROM @Stack s WHERE s.PrincipalID = rm.role_principal_id)
              AND NOT EXISTS (SELECT 1 FROM @Result r WHERE r.UserID = USER_NAME(rm.role_principal_id));
        ELSE -- 向下找子孫: 當前是群組，找成員
            INSERT @Stack
            SELECT rm.member_principal_id, @CurrentLevel + 1
            FROM sys.database_role_members rm
            WHERE rm.role_principal_id = @CurrentID
              AND NOT EXISTS (SELECT 1 FROM @Stack s WHERE s.PrincipalID = rm.member_principal_id)
              AND NOT EXISTS (SELECT 1 FROM @Result r WHERE r.UserID = USER_NAME(rm.member_principal_id));
    END

    RETURN;
END

關鍵設計點

• 使用表變數 @Stack 模擬遞迴堆疊：避免 T-SQL 遞迴函數 32 層限制與效能問題，支援任意深度。
• 去重邏輯：`NOT EXISTS` 檢查 @Stack 與 @Result，防止循環引用 (雖角色成員不應有環，但防禦性程式設計必要)。
• 系統目錄檢視：`sys.database_role_members` (資料庫角色成員)、`sys.server_role_members` (伺服器角色)、`sys.database_principals` / `sys.server_principals`。跨資料庫/伺服器需動態 SQL 或聯合查詢。
• BFS (廣度優先) vs DFS：範例採 BFS (依 LevelDepth 排序)，輸出層級順序直觀。DFS 只需移除 ORDER BY。

使用範例

-- 建立測試角色階層: Users > BusinessMan > Saler > OrderMan
EXEC sp_addrole 'Users';
EXEC sp_addrole 'BusinessMan'; EXEC sp_addrolemember 'Users', 'BusinessMan';
EXEC sp_addrole 'Saler'; EXEC sp_addrolemember 'BusinessMan', 'Saler';
EXEC sp_addlogin 'OrderMan', 'Pwd123', 'mydb'; EXEC sp_grantdbaccess 'OrderMan';
EXEC sp_addrolemember 'Saler', 'OrderMan';

-- 查詢 OrderMan 的所有祖先角色 (向上)
SELECT * FROM dbo.fn_GetUserInheritanceTree('OrderMan', 1);
-- 結果:
-- UserID    UserName    LevelDepth
-- 5         OrderMan    1
-- 102       Saler       2
-- 103       BusinessMan 3
-- 104       Users       4

-- 查詢 Users 角色包含的所有子孫成員 (向下)
SELECT * FROM dbo.fn_GetUserInheritanceTree('Users', 0);

現代替代方案 (SQL Server 2008+)

-- 使用遞迴 CTE (Common Table Expression) 更簡潔、集合導向、優化器友善
WITH cte AS (
    SELECT principal_id, name, 1 AS lvl, CAST(name AS VARCHAR(MAX)) AS path
    FROM sys.database_principals WHERE name = 'OrderMan'
    UNION ALL
    SELECT p.principal_id, p.name, cte.lvl + 1, cte.path + ' > ' + p.name
    FROM sys.database_principals p
    JOIN sys.database_role_members rm ON p.principal_id = rm.role_principal_id
    JOIN cte ON rm.member_principal_id = cte.principal_id
    WHERE cte.lvl < 100 -- 防無限遞迴
)
SELECT principal_id AS UserID, name AS UserName, lvl AS LevelDepth FROM cte;

推薦：新專案優先使用遞迴 CTE (可讀性、效能、平行化優於 WHILE 迴圈)。函數封裝適合舊版相容或需參數化複用的場景。