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.
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:
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.
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
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