Permissions when using "Execute sp_Executesql"

Question

I have a database where all access is controlled by stored procedures. The database administrator would like to avoid giving users direct read / write access to the underlying tables that I can understand. Therefore, all updating and data selection is done using stored procedures. Basically, he created one role that has EXECUTE permissions for all stored procedures in the database and provided users with this role.

The problem is that one of the stored procedures dynamically builds an SQl query and executes it through "Execute sp_Executesql". Without going into details, the query is created dynamically, since it changes significantly depending on many user input parameters. The stored procedure in question is only a SELECT sql statement, however, I believe that simply providing the EXECUTE permission stored procedure is not enough. In the base tables referenced in the stored procedure using "Execute sp_Executesql", access to the "datareader" must be granted, otherwise the stored procedure will fail.

Any thoughts on how to fix this? I really wanted to restrict access to tables to only stored procedures, but I need to find a way around the stored procedures that use "Execute sp_Executesq" l. Thanks.

Answer 1

In the proc package, you can use EXECUTE AS OWNER or EXECUTE AS SomeuserWithNoLogin

This will change the login context during the entire stored process, which includes sp_executesql.

• If you use OWNER, it will work because you are already using the ownership chain.
• If your database administrator (a good person!) Does not want you to work as dbo, then set up a user who has full read but does not have permission. EXECUTE AS <user> sys.database_principals entry sys.database_principals

Like this:

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

See EXECUTE AS Clause on MSDN and CREATE PROCEDURE for more information.

Answer 2

The real problem is that sp_Executesql is in the main database, not necessarily in the database you are working in. Your database administrator must grant sp_Executesql permission to the calling procedure. Those who have permission to call this procedure will be able to run sp_Executesql.