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

How can you check to see if a user can execute a stored procedure in MS SQL server?

如何检查用户是否可以在MS SQL服务器中执行存储过程？

I can see if the user has explicit execute permissions by connecting to the master database and executing:

我可以通过连接到master数据库并执行以下内容来查看用户是否具有显式执行权限：

databasename..sp_helpprotect 'storedProcedureName', 'username'

however if the user is a member of a role that has execute permissions sp_helprotect won't help me.

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

Ideally I'd like to be able to call something like

理想情况下，我希望能够打电话

databasename..sp_canexecute 'storedProcedureName', 'username'

which would return a bool.

这将返回一个布尔。

3 个解决方案

#1

fn_my_permissions and HAS_PERMS_BY_NAME

fn_my_permissions和HAS_PERMS_BY_NAME

#2

Try something like this:

尝试这样的事情：

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

#3

Assuming the SP only runs a SELECT statement:

假设SP只运行SELECT语句：

EXECUTE AS USER = [User's ID/Login]
EXEC sp_foobar( sna, fu)
REVERT

EXECUTE AS USER = [用户ID /登录] EXEC sp_foobar（sna，fu）REVERT

It's important to note that you will need to run the REVERT command after the prompt as SQL Server will regard you as the user you are EXECUTING AS until you either shut down the connection or REVERT the impersonation. That said, you should see exactly what a user would get (getting some rows but not all? This should help you out).

请务必注意，在提示符后需要运行REVERT命令，因为SQL Server会将您视为正在执行AS的用户，直到您关闭连接或重新模拟模拟为止。也就是说，你应该看到用户会得到什么（获得一些但不是全部？这应该可以帮助你）。

#1