Permissions when using "Execute sp_Executesql"

Question

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.

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.

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.

Answer 1

The real problem is that sp_Executesql is in the master database, not necessarily the database your working in. Your DBA has to give execute sp_Executesql permission to the calling procedure. Than anyone who has permission to call that procedure will be able to run the sp_Executesql.

Answer 2

In the wrapper proc you can use EXECUTE AS OWNER or EXECUTE AS SomeuserWithNoLogin

This will change the login context for the duration of the stored proc which includes sp_executesql.

• If you use OWNER, it will work because you're already using ownership chaining.
• If your DBA (good man!) does not want you running as dbo, then set up a user that has full read but no rights. EXECUTE AS <user> requires an entry is sys.database_principals

Like this:

CREATE USER SomeuserWithNoLogin WITH WITHOUT LOGIN
EXEC sp_addrolemember 'db_datareader', 'SomeuserWithNoLogin'

For more info, see EXECUTE AS Clause on MSDN and CREATE PROCEDURE