查询特定用户无执行权限存储过程的SQL问题求助

嗨，我明白你的困扰啦——明明知道用户对部分存储过程没权限，但用HAS_PERMS_BY_NAME查却没结果，这大概率是因为你没注意到这个函数的默认行为～

问题根源 #

HAS_PERMS_BY_NAME默认检查的是当前执行查询的用户的权限，而不是你要排查的那个特定用户。比如如果你用管理员账号跑这条语句，管理员通常拥有所有存储过程的执行权限，自然会返回空结果。

两种解决方案 #

方案1：模拟目标用户权限查询

如果你有切换到目标用户的权限，可以用EXECUTE AS临时切换身份，再执行查询：

-- 替换成你要检查的目标用户名
EXECUTE AS USER = 'TargetUserName';
GO

SELECT 
    name AS procedure_name,
    HAS_PERMS_BY_NAME(name, 'OBJECT', 'EXECUTE') AS has_execute_permission
FROM sys.procedures 
WHERE HAS_PERMS_BY_NAME(name, 'OBJECT', 'EXECUTE') = 0;

-- 切回原来的用户身份
REVERT;
GO

这样查询的就是目标用户的权限状态，能准确找出无执行权限的存储过程。

方案2：通过系统视图关联查询（无需切换用户）

如果没有切换用户的权限，或者不想切换身份，可以直接查询系统权限视图来过滤：

-- 替换成目标用户名
DECLARE @TargetUser NVARCHAR(128) = 'TargetUserName';

SELECT 
    p.name AS procedure_name
FROM sys.procedures p
LEFT JOIN (
    SELECT 
        dp.major_id
    FROM sys.database_permissions dp
    INNER JOIN sys.database_principals dpri 
        ON dp.grantee_principal_id = dpri.principal_id
    WHERE 
        dp.type = 'EX' -- EXECUTE权限的类型码
        AND dpri.name = @TargetUser
) user_perms 
    ON p.object_id = user_perms.major_id
WHERE user_perms.major_id IS NULL;

这个方法通过左关联权限表，找出没有匹配到EXECUTE权限的存储过程，结果和方案1一致，但不需要切换用户身份。

注意事项 #

• 确保目标用户存在于当前数据库中，否则查询会出错；
• sys.procedures只包含当前数据库的存储过程，如果要查其他库的，需要加上库名前缀（比如OtherDB.sys.procedures）；
• 如果你是用Windows域用户，记得用户名要写成DOMAIN\UserName的格式。

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