SQL Server 角色成員資格查詢與應用實戰

權限管理的演進：從個別授權到角色基礎存取控制

隨著用戶數量增長，為每位用戶單獨指派權限將變得難以維護。SQL Server 採用角色機制（類似 Windows 群組），將權限集合指派給角色，再將用戶加入角色，大幅簡化權限管理。

核心系統表結構

• sysusers：儲存用戶與角色資訊，IsSQLRole 欄位區分類型（1=角色，0=用戶）
• sysmembers：記錄角色與成員的對應關係

實用查詢腳本

1. 列出所有用戶及其所屬角色

SELECT 
    Member = Users.name, 
    Role = Roles.Name
FROM sysusers Users
INNER JOIN sysmembers Members ON Users.uid = Members.memberuid
INNER JOIN sysusers Roles ON Roles.uid = Members.groupuid
WHERE Roles.issqlrole = 1
ORDER BY 2, 1;

2. 查詢指定角色的所有成員

DECLARE @role VARCHAR(100) = 'Managers';

SELECT 
    MemberName = Users.name, 
    RoleName = Roles.Name
FROM sysusers Users
INNER JOIN sysmembers Members ON Users.uid = Members.memberuid
INNER JOIN sysusers Roles ON Roles.uid = Members.groupuid
WHERE Roles.name = @role
  AND Roles.issqlrole = 1
ORDER BY 2, 1;

3. 封裝為使用者定義函數（UDF）—— 應用程式整合最佳實踐

CREATE FUNCTION dbo.fn_IsUserInRole (
    @username SYSNAME,
    @rolename SYSNAME
)
RETURNS BIT
AS
BEGIN
    DECLARE @result BIT = 0;
    
    IF EXISTS (
        SELECT 1
        FROM sysusers u
        INNER JOIN sysmembers m ON u.uid = m.memberuid
        INNER JOIN sysusers r ON r.uid = m.groupuid
        WHERE u.name = @username
          AND r.name = @rolename
          AND r.issqlrole = 1
    )
        SET @result = 1;
        
    RETURN @result;
END;
GO

-- 使用範例
IF dbo.fn_IsUserInRole('john_doe', 'Managers') = 1
    PRINT 'User has manager privileges';

進階應用場景

• 動態資料列級安全性： 在 View 或 Stored Procedure 中呼叫 UDF，根據角色篩選資料
• 應用程式層授權： 中介軟體呼叫 UDF 決定功能按鈕顯示/隱藏
• 審計報表： 定期產生角色成員名冊，滿足合規要求

現代化建議（SQL Server 2005+）

新版本建議使用 sys.database_principals 與 sys.database_role_members 目錄檢視，並搭配 IS_ROLEMEMBER() 內建函數：

SELECT IS_ROLEMEMBER('db_datareader', 'john_doe'); -- 返回 1/0/NULL

掌握角色查詢技巧，能讓權限管理從「惡夢」變為可控、可審計的系統工程。