SQL Server 2000 物件權限查詢預存程序 usp_getObjectAuthor

功能說明

查詢指定物件（資料表、檢視表、預存程序等）的 GRANT/DENY/REVOKE 權限，或查詢指定使用者/角色的所有物件權限。支援跨資料庫查詢。

參數

回傳欄位

• objectname：物件名稱
• usename：使用者/角色名稱
• Role：'Group'（角色）或 'User'
• ProtectType：'Grant'、'Deny'、'Revoke'
• Action：權限動作（SELECT、INSERT、UPDATE、DELETE、EXECUTE、REFERENCES 等）

核心查詢邏輯

SELECT 
    OBJECT_NAME(a.id) AS objectname,
    USER_NAME(a.uid) AS usename,
    CASE b.issqlrole WHEN 1 THEN 'Group' ELSE 'User' END AS Role,
    CASE a.protecttype 
        WHEN 204 THEN 'Grant' WHEN 205 THEN 'Grant' 
        WHEN 206 THEN 'Deny' ELSE 'Revoke' END AS ProtectType,
    CASE a.action
        WHEN 193 THEN 'SELECT' WHEN 195 THEN 'INSERT'
        WHEN 196 THEN 'DELETE' WHEN 197 THEN 'UPDATE'
        WHEN 224 THEN 'EXECUTE' WHEN 26 THEN 'REFERENCES'
        -- 其餘動作代碼對應...
    END AS Action
FROM sysprotects a
JOIN sysusers b ON a.uid = b.uid
WHERE (@objectname IS NULL OR OBJECT_NAME(a.id) = @objectname)
  AND (@username IS NULL OR USER_NAME(a.uid) = @username);

使用範例

-- 查詢所有物件權限
EXEC usp_getObjectAuthor;

-- 查詢特定資料表權限
EXEC usp_getObjectAuthor @objectname = 'Orders';

-- 查詢特定使用者權限
EXEC usp_getObjectAuthor @username = 'app_user';

注意

• SQL Server 2005+ 建議改用 sys.database_permissions、fn_my_permissions、HAS_PERMS_BY_NAME 等目錄檢視/函數。
• 本程序僅適用 SQL Server 2000 系統資料表結構。