Should CONTROL permission be given on a Stored Procedure in SQL Server 2005?

Question

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?

Thanks in advance.

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.

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.

Answer 1

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.

From the SQL Server documentation for EXECUTE AS:

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.

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.

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

Answer 2

Execute should be all that is needed.

Is the stored procedure accessing a table outside of the database it is located in?

If so, try setting the appropriate user permissions on the tables the stored procedure is using outside of the same database.

Answer 3

If you only need to be able to execute the stored procedure then obviously the CONTROL permission is not the way to go. Yes it works, the same way it works running your web site under the local system account.

If the grantor of the EXECUTE permission is also the owner of the tables that get affected then there should be no problem executing the sp. Otherwise you should grant explicit permissions or consider adjusting the ownership with the ALTER AUTHORIZATION statement.

For bonus manageability create a database role to apply explicit permissions instead of assigning them directly to users.