MS SQL Server 2008 R2数据库级权限查询：求系统视图/函数方案

嘿，我懂你想要的——不用切换到每个用户执行fn_my_permissions，直接一次性列出数据库层面所有已授予的权限对吧？其实SQL Server 2008 R2里早就有对应的系统视图了，只是需要把几个视图关联起来，才能得到可读性强的结果。

基础版：直接列出所有数据库级权限（含用户/角色） #

sys.database_permissions就是你要找的核心系统视图，它记录了数据库内所有权限的授予/拒绝记录，但单独查询的话全是ID，可读性差。我们可以关联sys.database_principals（存储数据库用户、角色等主体）来获取清晰的名称信息：

USE [YourDatabaseName]; -- 替换成你要查询的目标数据库名
GO

SELECT
    dp.state_desc AS 权限状态,
    dp.permission_name AS 权限名称,
    grantee.name AS 被授权者,
    grantor.name AS 授权者,
    '数据库' AS 权限作用对象
FROM 
    sys.database_permissions dp
JOIN 
    sys.database_principals grantee ON dp.grantee_principal_id = grantee.principal_id
JOIN 
    sys.database_principals grantor ON dp.grantor_principal_id = grantor.principal_id
WHERE 
    dp.class = 0; -- class=0 专门筛选数据库级权限，排除对象级权限
ORDER BY 
    grantee.name, dp.permission_name;

脚本关键部分解释： #

• dp.state_desc：显示权限的具体状态，比如GRANT（直接授予）、GRANT WITH GRANT OPTION（授予且允许转授）、DENY（拒绝）。
• dp.permission_name：具体的权限类型，比如SELECT、EXECUTE、ALTER、BACKUP DATABASE等。
• grantee.name：被授予权限的用户或角色名称。
• grantor.name：执行授权操作的用户（通常是管理员或数据库所有者）。

进阶版：包含用户从角色继承的权限 #

上面的脚本只会显示直接授予的权限，如果要列出用户实际拥有的所有权限（包括从所属角色继承的），可以用递归CTE来遍历角色层级，再关联权限视图：

USE [YourDatabaseName];
GO

-- 递归CTE遍历所有用户的角色从属关系
WITH RoleHierarchy AS (
    -- 直接关联用户和其所属的角色
    SELECT 
        member.principal_id AS UserID,
        member.name AS UserName,
        role.principal_id AS RoleID,
        role.name AS RoleName
    FROM 
        sys.database_role_members rm
    JOIN 
        sys.database_principals member ON rm.member_principal_id = member.principal_id
    JOIN 
        sys.database_principals role ON rm.role_principal_id = role.principal_id
    UNION ALL
    -- 递归查询角色的父角色（处理嵌套角色）
    SELECT 
        rh.UserID,
        rh.UserName,
        role.principal_id AS RoleID,
        role.name AS RoleName
    FROM 
        RoleHierarchy rh
    JOIN 
        sys.database_role_members rm ON rh.RoleID = rm.member_principal_id
    JOIN 
        sys.database_principals role ON rm.role_principal_id = role.principal_id
)
-- 合并角色继承的权限和用户直接被授予的权限
SELECT DISTINCT
    dp.state_desc AS 权限状态,
    dp.permission_name AS 权限名称,
    rh.UserName AS 用户名称,
    grantor.name AS 授权者,
    '数据库' AS 权限作用对象
FROM 
    sys.database_permissions dp
JOIN 
    RoleHierarchy rh ON dp.grantee_principal_id = rh.RoleID
JOIN 
    sys.database_principals grantor ON dp.grantor_principal_id = grantor.principal_id
WHERE 
    dp.class = 0
UNION ALL
SELECT
    dp.state_desc AS 权限状态,
    dp.permission_name AS 权限名称,
    grantee.name AS 用户名称,
    grantor.name AS 授权者,
    '数据库' AS 权限作用对象
FROM 
    sys.database_permissions dp
JOIN 
    sys.database_principals grantee ON dp.grantee_principal_id = grantee.principal_id
JOIN 
    sys.database_principals grantor ON dp.grantor_principal_id = grantor.principal_id
WHERE 
    dp.class = 0
    AND grantee.type_desc = 'SQL_USER' -- 只筛选普通用户，排除角色等主体
ORDER BY 
    用户名称, 权限名称;

为什么你之前可能觉得找不到合适的视图？ #

sys.database_permissions确实存在，但默认查询返回的grantee_principal_id是数字ID，没有关联主体视图的话根本看不懂对应的用户/角色。只要把它和sys.database_principals关联起来，就能得到你想要的清晰结果啦。

内容的提问来源于stack exchange，提问作者FCH