I have granted a user execute permission for a stored procedure that uses dynamic SQL. But when he tries to execute it, he gets the error:
The SELECT permission was denied on the object '[table name]', database '[database name]', schema 'dbo'.
Does the user need to be granted permission for any tables that the stored procedure uses? That wouldn't really make any sense to me.
Best Answer
Ok, on the basis of the above comment and as per my suspicion - it seems as though you are trying to execute dynamic SQL within your stored procedure.
What you need to remember is that when you do this it does not get executed within the context of the stored procedure - it gets executed within a new session. Because of this, the fact that the statement is being called within a stored procedure is a moot point, and you will need to grant explicit permission on the objects that your dynamic SQL is using.
If you don't want to do this I would refactor your stored procedure to not use dynamic SQL.
The below link from Microsoft should help you with your problem:
PRB: Security Context of Dynamic SQL Statements Inside a Stored Procedure (Wayback Machine archive)
This is also discussed in the (more current) Microsoft Docs article:
Writing Secure Dynamic SQL in SQL Server