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.

Disturbing

You may get better avdice serverfault. My advice - Talk to the dba and explain the situation. Work with them to get the permissions right. – Properly

Answer 1

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

Tribulation

This other thread you pointed out below also helps with understanding this.... #3815911 – Sweetbrier

Answer 2

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.

Romp

-1 sp_Executesql has public execute already. "Requires membership in the public role." msdn.microsoft.com/en-us/library/ms188001.aspx The real problem is ownership chaining breaks when you use sp_executesql See this stackoverflow.com/questions/3815411 – Tribulation

If you've hardened your database to lockdown the public role, then @Romp is correct; for instance, you've created a custom role to replace the public role and removed all privileges from public. Yes, this goes against documented requirements but database hardening scanning systems like AppDetective (via SQL Server STIGs) present the public role and its default open access as a major risk. – Pleven

...besides, quoting that same MSDN article, "Run time-compiled Transact-SQL statements can expose applications to malicious attacks." This is more prominently displayed than the "Requires membership in the public role" statement. – Pleven