One of the TRUSTWORTHY features within SQL Server 2005 will be like when you are running a stored procedure with a dynamic execution query (sp_executesql or EXECUTE), you may receive the following error message:
Server Msg 229, Level 14, State 5, Line 1 'permission' permission denied on object 'object', database 'database', owner 'owner'.
As a Developer or DBA you wouldn't get confusion when trying to execute the above piece of code 'sp_executesql' as it is specific there explicitly, how about when this returns within a stored procedure execution. It is obvious that few of the processes will be executdd under a user login which may have DBO permissions and such confusion can occur as all the tables are owned by dbo, and all stored procedures are owned by dbo.
Dynamic SQL is easy to get the things around when you do not have the direct permission on the tables, but sp_executesql has a different scope from the stored procedure it is called from, and it doesn't inherit the permissions from the stored procedure. So the statement inside sp_executesql is run with the permissions of the user who called the stored procedure, and that user might not have permissions on the table. Erland Sommarskog (SQL Server MVP) has given an excellent indepth of information about Curse-Blessings_of_DynamicSQL which is a definitieve guide for the Developers & DBAs and also I recommend to visit Fun_and_Fumes_DynamicSQL-Injection issues blog post that might affect the security of your database system.
Coming to the issue resoution either you have to remove all the permissions to EXECUTE sp_executesql on that SQL instance or grant SELECT permissions on the stored procedure. Due to the reason that this behavior occurs because a dynamic execution query (sp_executesql or EXECUTE) executes in a separate context from the main stored procedure; it executes in the security context of the user that executes the stored procedure and not in the security context of the owner of the stored procedure.
If you have deployed ownership chains then you must take the above approach into account while you are performing the process to grant the necessary permission for each underlying object that is mentioned in the dynamic execution query.