SQL Server 權限管理：遞迴查詢使用者角色繼承樹

需求：給定使用者，查詢其所有子孫 (成員) 或祖先 (所屬角色) 階層。利用系統表 sysmembers (舊版) 或 sys.database_role_members (新版) 配合遞迴 CTE 實作。

舊版 sysmembers 版本 (SQL 2000 相容)

CREATE FUNCTION dbo.getUserTree
(
  @UserName SYSNAME,
  @Seq BIT  -- 0=查子孫(成員), 1=查祖先(角色)
)
RETURNS @Result TABLE (UserID SYSNAME, UserName SYSNAME, Level INT)
AS
BEGIN
  DECLARE @UserId SYSNAME = USER_ID(@UserName);
  IF @UserId IS NULL RETURN;

  DECLARE @level INT = 1;
  DECLARE @stack TABLE (item SYSNAME, level INT);
  INSERT @stack VALUES (@UserId, 1);

  WHILE @level > 0
  BEGIN
    IF EXISTS (SELECT 1 FROM @stack WHERE level = @level)
    BEGIN
      SELECT @UserId = item FROM @stack WHERE level = @level;
      INSERT @Result VALUES (@UserId, USER_NAME(@UserId), @level);
      DELETE FROM @stack WHERE level = @level AND item = @UserId;

      IF @Seq = 1  -- 向上找角色
        INSERT @stack SELECT groupuid, @level + 1 FROM sysmembers WHERE memberuid = @UserId;
      ELSE       -- 向下找成員
        INSERT @stack SELECT memberuid, @level + 1 FROM sysmembers WHERE groupuid = @UserId;

      IF @@ROWCOUNT > 0 SET @level = @level + 1;
    END
    ELSE
      SET @level = @level - 1;
  END
  RETURN;
END

現代版：遞迴 CTE (SQL 2005+)

-- 查詢使用者所有角色 (遞迴向上)
WITH RoleHierarchy AS (
  SELECT rm.member_principal_id AS UserId, rm.role_principal_id AS RoleId, 1 AS Level
  FROM sys.database_role_members rm
  WHERE rm.member_principal_id = USER_ID('target_user')
  UNION ALL
  SELECT rh.UserId, rm.role_principal_id, rh.Level + 1
  FROM RoleHierarchy rh
  JOIN sys.database_role_members rm ON rm.member_principal_id = rh.RoleId
)
SELECT USER_NAME(UserId) AS UserName, USER_NAME(RoleId) AS RoleName, Level
FROM RoleHierarchy;

使用範例

-- 查 user1 所有子孫 (成員)
SELECT * FROM dbo.getUserTree('user1', 0);
-- 查 user1 所有祖先 (角色)
SELECT * FROM dbo.getUserTree('user1', 1);