I have a user in the database that has the following permissions:
test_role only has permissions to execute certain stored procedures. One of the procedures makes use of the SP_EXECUTESQL system sproc. When executing the sproc an error is generated that states that select permissions are denied on tables that are used in the query executed by SP_EXECUTESQL. When checking the db_owner role for that user the sproc works. The problem is I don't want to grant db_owner status to that user. How can I get around this permission problem and allow users that have only public and test_role permissions to make use of SP_EXECUTESQL?