存储过程和权限——执行得够吗?

I have a SQL Server 2008 database where all access to the underlying tables is done through stored procedures. Some stored procedures simply SELECT records from the tables while others UPDATE, INSERT, and DELETE.

我有一个SQL Server 2008数据库，所有对底层表的访问都是通过存储过程完成的。一些存储过程只是从表中选择记录，而另一些存储过程则更新、插入和删除。

If a stored procedure UPDATES a table does the user executing the stored procedure also need UPDATE permissions to the affected tables or is the fact that they have EXECUTE permissions to the stored procedure enough?

如果存储过程更新了一个表，那么执行存储过程的用户是否也需要对受影响的表进行更新权限呢?

Basically I am wondering if giving the user EXECUTE permissions to the stored procedures is enough or do I need to give them SELECT, UPDATE, DELETE, and INSERT permissions to the tables in order for the stored procedures to work. Thank you.

基本上，我想知道给用户执行存储过程的权限是否足够，或者我需要给他们选择、更新、删除和插入表的权限，以便存储过程工作。谢谢你！

[EDIT] In most of my stored procedures it does indeed appear that EXECUTE is enough. However, I did find that in stored procedures where "Execute sp_Executesql" was used that EXECUTE was not enough. The tables involved needed to have permissions for the actions being performed within "sp_Executesql".

在我的大多数存储过程中，似乎执行就足够了。但是，我确实发现在存储过程中，使用“Execute sp_Executesql”执行是不够的。涉及的表需要具有在“sp_Executesql”中执行的操作的权限。

5 个解决方案

#1

Execute permissions on the stored procedure is sufficient.

对存储过程的执行权限是充分的。

CREATE TABLE dbo.Temp(n int)

GO
DENY INSERT ON dbo.Temp TO <your role>
GO
CREATE PROCEDURE dbo.SPTemp(@Int int)
AS

INSERT dbo.Temp
SELECT  @Int 

GO

GRANT EXEC ON dbo.SPTemp TO <your role>

GO

Then the (non-db_owner) user will have the following rights:

然后(非db_owner)用户将拥有以下权利:

EXEC dbo.SPTemp 10
GO

INSERT dbo.Temp --INSERT permission was denied on the object 'Temp'
SELECT  10

However, if there is dynamic SQL inside dbo.SPTemp that attempts to insert into dbo.Temp then that will fail. In this case direct permission on the table will need to be granted.

但是，如果dbo内部有动态SQL的话。试图插入到dbo中的SPTemp。那就会失败。在这种情况下，需要授予表上的直接权限。

#2

Permissions on tables are not checked (including DENY) if tables and proc have the same owner. They can be in different schemas too as long as the schemas have the same owner.

如果表和proc具有相同的所有者，则不检查表的权限(包括DENY)。只要模式具有相同的所有者，它们也可以处于不同的模式中。

See Ownership chaining on MSDN

参见MSDN上的所有权链接

Edit, from a comment from a deleted answer.

编辑，从一个被删除的答案的评论。

The context is always the current login unless EXECUTE AS as been used: only referenced object DML permissions are not checked. Try OBJECT_ID(referencedtable) in a stored proc where no rights are assigned to referencedtable. It gives NULL. If executed by the owner of the stored proc then it would give a value because owener has rights on referencedtable

上下文始终是当前登录，除非被使用:只有引用的对象DML权限没有被检查。在一个存储的proc中尝试OBJECT_ID(referencedtable)，在那里没有分配给referencedtable的权限。它给零。如果由存储proc的所有者执行，那么它将给出一个值，因为owener对referencedtable具有权限

#3

Execute permission on a stored procedure that does an insert, update, or delete is sufficient. You do not need to grant those permissions at the table level. In fact, I would discourage that approach. Using a stored procedure gives you more control over how the change occurs. For instance, you may wish to do some checking prior to allowing the update. Using a stored procedure can also help prevent major accidents--like deleting all the rows in the table because someone forgot the WHERE clause!

对执行插入、更新或删除的存储过程执行权限就足够了。您不需要在表级别授予这些权限。事实上，我反对这种做法。使用存储过程可以对更改的发生方式进行更多的控制。例如，您可能希望在允许更新之前进行一些检查。使用存储过程还可以帮助防止重大事故——比如删除表中的所有行，因为某人忘记了WHERE子句!

#4

Maybe you can use

也许你可以使用

"with execute as owner"

与执行“所有者”

when you create the stored procedure, such as below:

当您创建存储过程时，例如:

create procedure XXX
with execute as owner
as
begin
...
end
go

Then you only need to grant the user the EXECUTE permission for the stored procedure XXX.

然后，您只需要为存储过程XXX授予用户执行权限。

#5

THANK YOU SO MUCH! I had a similar problem. This lead me to the answer.

谢谢你这么多!我也有类似的问题。这使我找到了答案。

I was attempting to trunctate a table in a stored procedure that called other stored procedures that were nested in IF statements.

我试图截断存储过程中的一个表，该存储过程调用IF语句中嵌套的其他存储过程。

My error was

我的错误是

The server principal "domain\my_id" is not able to access the database "2nd_DB" under the current security context.

服务器主体“domain\my_id”不能在当前安全上下文中访问数据库“2nd_DB”。

I had given the calling stored procedure rights to do the truncate (EXECUTE AS SELF), which then caused a problem because SELF didn't have rights to the 2nd DB. Our solution was to move the truncate to another SP, include the EXECUTE AS SELF. We now call the truncate SP, execute our data processing, make logic determination, and call the appropriate 3rd SP.

我给了调用存储过程的权限来执行截断(作为SELF执行)，这导致了一个问题，因为SELF没有对第二个DB的权限。我们的解决方案是将截断项移动到另一个SP，将EXECUTE作为SELF。现在我们调用truncatetable SP，执行数据处理，进行逻辑判断，并调用适当的第三个SP。

#1