MS SQL Server: 检查用户是否可以执行存储过程

Question

如何检查用户是否能在MS SQL Server中执行存储过程？

我可以连接到主数据库并执行以下操作，以查看用户是否具有显式的执行权限：

databasename..sp_helpprotect 'storedProcedureName', 'username'

然而，如果用户是具有执行权限的角色成员，则 sp_helprotect 无法帮助我。

理想情况下，我希望能够调用类似于

databasename..sp_canexecute 'storedProcedureName', 'username'

这将返回一个布尔值。

- Andrew

---

此外，如果您正在使用SQL Server 2005或更高版本，并且权限已分配给模式或数据库（现在可以执行），则sp_helprotect不会报告它。该存储过程仅用于向后兼容，并根据SQL Server 2000中的内容报告权限。 - K. Brian Kelley

Answer 1

fn_my_permissions and HAS_PERMS_BY_NAME

- Cade Roux

---

我认为这些函数并没有回答原始问题，即检查任何给定用户是否对给定的存储过程具有执行权限。两个函数的MSDN文章都说它们仅返回调用主体的权限，而不是任何主体的权限。 - Simon Elms

Answer 2

尝试类似于以下的代码：

CREATE PROCEDURE [dbo].[sp_canexecute]
@procedure_name varchar(255),
@username varchar(255),
@has_execute_permissions bit OUTPUT
AS

IF EXISTS (
        /* Explicit permission */
        SELECT 1
        FROM sys.database_permissions p
        INNER JOIN sys.all_objects o ON p.major_id = o.[object_id] AND o.[name] = @procedure_name
        INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id AND dp.[name] = @username
    )
    OR EXISTS (
        /* Role-based permission */
        SELECT 1
        FROM sys.database_permissions p
        INNER JOIN sys.all_objects o ON p.major_id = o.[object_id]
        INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id AND o.[name] = @procedure_name
        INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
        INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id AND dp2.[name] = @username
    )
BEGIN
    SET @has_execute_permissions = 1
END
ELSE
BEGIN
    SET @has_execute_permissions = 0
END
GO

- Dane

---

如果用户是具有权限的角色成员，并且未被明确允许执行存储过程，那么这是否有效？ - Andrew

不可以，但你可以将角色名放入@username参数中，这样仍然能返回正确/预期的布尔结果。 - Dane

好的，我已更新上面的过程代码，可以通过角色授予权限，也可以通过显式授权。 - Dane

我认为这并没有考虑通过父权限授予的权限，比如用户是否对整个模式具有执行权限，但对该项没有直接权限。这准确吗？ - Maslow

Answer 3

假设SP仅运行SELECT语句：

EXECUTE AS USER = [用户ID / 登录名]
EXEC sp_foobar(sna, fu)
REVERT

需要注意的是，在提示后，您需要运行REVERT命令，因为SQL Server会将您视为EXECUTING AS的用户，直到您关闭连接或REVERT模拟。 也就是说，您应该能够看到用户所得到的内容（获取了一些行但不是全部？这应该能帮助您）。

- Pulsehead