Executing a stored procedure which selects and inserts into tables in SQL Server

Question

I am a bit confused on the permissions required to execute stored procedure. For example, if you have 2 tables and a stored procedure which reads data from Table1 and inserts data into Table2, will execute permission on the stored procedure and select permission on the tables be enough? In theory I am finding that this is given as a solution but practically I am finding that a user would need execute permission on the stored procedure, select permission on the tables and insert permission on Table2.

Answer 1

No, you don't need to grant explicit permission on Table1 and Table2, that's one of the objective of embedding code in stored procedure and that's where encapsulation feature comes into effect.

Please check below link from Microsoft:

Managing Permissions with Stored Procedures in SQL Server

Stored Procedure Execution

Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.

Use the code below to grant execute permission:

USE database_name
GO
GRANT EXECUTE ON USP_NAME  
    TO User_name;  
GO

Hope above helps.

Answer 2

There is at least one exception to Learning_DBAdmin's correct answer that I know of: dynamic SQL

Imagine usp_Proc1 that looks like this:

CREATE PROCEDURE dbo.usp_Proc1  
AS 

SELECT TOP (10) FROM dbo.Users;

If you have "EXECUTE" permissions to usp_Proc1, then calling EXEC dbo.usp_Proc1 will work just fine.

However, if you change the implementation of the procedure to use dynamic SQL:

CREATE PROCEDURE dbo.usp_Proc1  
AS 

EXEC sp_executesql 'SELECT TOP (10) FROM dbo.TableA';

The call to EXEC dbo.usp_Proc1; will fail with a permissions error unless you also grant the user SELECT permissions on dbo.TableA.

Running code with sp_executesql or EXECUTE like that creates a new "batch" outside of the context of the execution of the stored procedure.

Generally, this is because of the way ownership chaining works, which is described nicely here: Broken ownership chain – Dynamic SQL