title: Permissions when using "Execute sp_Executesql"
body: <p>I have a database where all access is controlled by stored procedures. The DBA would like to avoid giving users direct read/write access to the underlying tables, which I can understand. Hence all updating and selecting of data is done via stored procedures. Basically he has created one role that has EXECUTE permissions to all the stored procedures in the database and given users that role.</p>
  
  <p>The problem is that one of the stored procedures dynamically builds a SQl Query and executes it via "Execute sp_Executesql". Without going into great detail the query is built dynamically because it changes significantly depending on many user input parameters. The stored procedure in question is only a SELECT sql statement however I am finding that just giving the stored procedure EXECUTE permission is not enough. The underlying tables referenced within the stored procedure that make use of "Execute sp_Executesql" need to have been given "datareader" access or else the stored procedure fails. </p>
  
  <p>Any thoughts on how to correct this? I really wanted to restrict access to the tables to only stored procedures, but I need to find a way to work around the stored procedures that make use of "Execute sp_Executesq"l. Thank you.</p>