MS SQL Server：检查用户是否可以执行存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/484145/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MS SQL Server: Check to see if a user can execute a stored procedure
提问by Andrew
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:
我可以通过连接到主数据库并执行来查看用户是否具有显式执行权限：
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.
这将返回一个布尔值。
回答by Cade Roux
回答by Dane
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
回答by Pulsehead
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 会将您视为您正在执行的用户，直到您关闭连接或 REVERT 模拟为止。也就是说，您应该确切地看到用户会得到什么（获取一些行但不是全部？这应该对您有所帮助）。
