如何查询SQL Server自定义角色的存储过程执行权限？解决has_perms_by_name困惑

我明白你在尝试找出SQL Server里自定义角色能执行的存储过程时，卡在了has_perms_by_name函数的使用上——这个函数的参数确实容易让人晕，我来一步步帮你搞懂，同时给你两种靠谱的查询方法。

方法1：正确使用has_perms_by_name函数 #

这个函数的核心是帮你检查指定主体（比如角色）对某个对象是否拥有特定权限，针对存储过程和角色的场景，参数要这么填：

-- 先替换成你要查询的自定义角色名称
DECLARE @TargetRole NVARCHAR(128) = N'YourCustomRole';

SELECT 
    SCHEMA_NAME(proc.schema_id) AS 架构名,
    proc.name AS 存储过程名,
    'EXECUTE' AS 权限类型
FROM 
    sys.procedures proc
WHERE 
    HAS_PERMS_BY_NAME(
        -- 第一个参数：要检查的对象全名（架构+存储过程，用QUOTENAME避免特殊字符问题）
        QUOTENAME(SCHEMA_NAME(proc.schema_id)) + '.' + QUOTENAME(proc.name),
        -- 第二个参数：对象类型，存储过程属于OBJECT
        'OBJECT',
        -- 第三个参数：要检查的权限类型，这里是EXECUTE
        'EXECUTE',
        -- 第四个参数：主体类型，我们查的是角色，所以填ROLE
        'ROLE',
        -- 第五个参数：要查询的角色名称
        @TargetRole
    ) = 1
ORDER BY 
    架构名, 存储过程名;

参数拆解 #

• 第一个参数必须是对象的完整限定名（架构+对象名），用QUOTENAME是为了处理那些带空格、特殊字符的名称，避免语法错误。
• 主体类型填ROLE是关键，如果你漏了这个，函数会默认检查当前登录用户的权限，而不是目标角色的。
• 返回值为1就代表该角色拥有对应的执行权限。

方法2：通过系统视图直接查询（更直观） #

如果你觉得函数用起来麻烦，也可以直接关联SQL Server的系统视图，这种方式能直接看到权限的分配情况，还能一次性查所有自定义角色的权限：

SELECT 
    role_princ.name AS 角色名称,
    SCHEMA_NAME(obj.schema_id) AS 架构名,
    obj.name AS 存储过程名,
    perm.state_desc + ' ' + perm.permission_name AS 权限详情
FROM 
    sys.database_permissions perm
-- 关联到角色主体
JOIN sys.database_principals role_princ 
    ON perm.grantee_principal_id = role_princ.principal_id
-- 关联到存储过程对象
JOIN sys.objects obj 
    ON perm.major_id = obj.object_id
WHERE 
    role_princ.type = 'R' -- 只筛选角色类型的主体
    AND role_princ.is_fixed_role = 0 -- 排除系统自带的固定角色
    AND obj.type = 'P' -- 只筛选存储过程
    AND perm.permission_name = 'EXECUTE' -- 只看执行权限
ORDER BY 
    角色名称, 架构名, 存储过程名;

两种方法的区别 #

• has_perms_by_name会自动考虑继承的权限（比如你的自定义角色属于另一个拥有执行权限的角色，这种情况也会被检测到）。
• 系统视图的查询默认只显示直接分配给角色的权限，如果要包含继承的权限，需要额外处理，或者直接用第一种方法更省心。

小提示 #

• 替换角色名称时，记得用单引号包裹，区分大小写的话要保持和数据库里的角色名一致。
• 如果你的存储过程在dbo以外的架构下，一定要带上架构名，不然可能查不全。

内容的提问来源于stack exchange，提问作者C. Gabriel