是否应该对SQL Server 2005中的存储过程赋予CONTROL权限? [英] Should CONTROL permission be given on a Stored Procedure in SQL Server 2005?

问题描述

我遇到一个问题，在SQL Server 2005中授予对特定存储过程的EXECUTE权限不起作用.一些测试人员弄乱了权限-并发现，如果他们还对存储过程授予了CONTROL权限-则运行正常.现在，他们坚信授予CONTROL权限是必经之路.

I'm running into an issue where granting EXECUTE permissions on a specific Stored Procedure in SQL Server 2005 is not working. Some of the testers messed around with the permissions - and found that if they also granted CONTROL permissions on the Stored Procedure - then it ran fine. They are now convinced that granting CONTROL permissions is the way to go.

我知道这不是真的-实际上，我认为真正的问题是用户对存储过程所针对的表没有选择/插入/更新/删除权限.问题是，我似乎无法在网上找到任何可以证明这一点的东西.

I know this can't be true - and in fact I think that the real problem is that the user did not have Select/Insert/Update/Delete permissions to the tables which the Stored Procedure ran against. The problem is, I can't seem to find anything online that proves it.

我正确吗?是否有人知道有关此的任何文档?

Am I correct? Is anybody aware of any documentation that talks about this?

谢谢.

更多信息以回应评论: 该存储过程正在执行多个删除.它首先删除所有要删除的主"记录所孤立的记录，然后最后删除父记录.

More info in response to comments: The stored procedure is doing multiple deletes. It first deletes all of the records that would be orphaned by the "main" record being deleted, and then finally deletes the parent record.

此外，我们看到的错误表明用户没有足够的权限-或存储过程不存在.我们已经确认我们使用的是正确的用户，并且已向该用户授予EXECUTE权限.

Also, the error that we see says that the user doesn't have sufficient permissions - or the Stored Procedure doesn't exist. We've already confirmed that we're using the right user, and that EXECUTE permissions were given to that user.

推荐答案

如果存储过程是使用EXECUTE AS CALLER创建的(我相信这是默认设置)，则调用者必须具有执行所有操作所需的所有权限.存储过程除了对过程执行EXECUTE之外.

If the stored procedure was created using EXECUTE AS CALLER (which I believe is the default), then the caller must have all of the permissions necessary to do whatever the stored procedure does in addition to EXECUTE on the procedure.

从SQL Server文档中获取EXECUTE AS:

From the SQL Server documentation for EXECUTE AS:

CALLER指定内部的语句 该模块在上下文中执行 模块调用者的名称.用户 执行模块必须有 适当的权限不仅限于 模块本身，也可以在任何模块上 引用的数据库对象 通过模块.

CALLER Specifies the statements inside the module are executed in the context of the caller of the module. The user executing the module must have appropriate permissions not only on the module itself, but also on any database objects that are referenced by the module.

请注意，由于SQL Server使用所有权链处理权限检查的方式，并非总是严格如此，并且我猜想在过程上授予CONTROL(将所有权状态授予被授予者)会导致这些权限要绕过的检查.

Note that because of the way SQL Server processes permission checks using ownership chains, this isn't always strictly true, and I'm guessing that granting CONTROL on the procedure (which confers ownership status to the grantee) is causing these permission checks to be bypassed.

如果使用EXECUTE AS OWNER创建该过程，则除了该过程的EXECUTE之外，您无需授予任何其他权限.

If you create the procedure with EXECUTE AS OWNER, then you should not need to grant any permissions beyond EXECUTE on the procedure.

这篇关于是否应该对SQL Server 2005中的存储过程赋予CONTROL权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！