Executing a stored procedure which selects and inserts into tables in SQL Server 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:

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.