SQL Server Permissions on Stored Procs with dynamic SQL

Question

I have a database which has an application role. The role members all belong to a group in Active Directory. Instead of giving the role permissions to select from the tables I have given the role execute permissions on all of the stored procedures that it needs to call.

This works fine except for one of my stored procedures which is building up some dynamic SQL and calling sp_executesql.

The dynamic sql looks sort of like this:

SET @SQL = N'
SELECT * 
FROM dbo.uvView1 
INNER JOIN uvView2 ON uvView1.Id = uvView2.Id'

EXEC sp_executesql @SQL

The users in this role are failing to call the stored procedure. It gives the following error which is sort of expected I suppose:

The SELECT permission was denied on the object 'uvView1', database 'Foobar', schema 'dbo'.

Is there a way I can have my users successfully execute this proc without giving the role permissions to all of the views in the dynamic SQL?

Senter

The user is accessing the view, right? I don't think security cares how they access it. – Chokefull

This is the only proc that is failing and it's not failing with execute permissions on the proc...it is specifically telling me it can SELECT from the view...so I'm guessing it does matter somehow. – Senter

As to why this happens I found Dynamic SQL and Ownership Chaining in SQL Server @ mssqltips.com useful: "When either sp_executesql or the EXECUTE statement executes a string, the string is executed as its own self-contained batch. ... permissions are required on the securables that are referenced within the EXECUTE string" – Azedarach

Answer 1

Yes.

Add an EXECUTE AS CALLER clause to the procedure, then sign the stored procedure and give the required permission to the signature. This is 100% safe, secure and bullet proof. See Signing Procedures with Certificates.

Soteriology

How about a solution using a database trigger to monitoring the Sql of the user and reject it if the SQL start with select? – Gorey

As the linked article says, WITH EXECUTE AS CALLER is the default. And the signing process, while unpleasant, is not too complicated, requiring only a few SQL commands (CREATE CERTIFICATE+CREATE USER cert$user FROM CERTIFICATE .., followed by ADD SIGNATURE TO @spname BY CERTIFICATE .. and GRANT ... TO cert$user) – Azedarach

Answer 2

Can you use impersonation to another ID with the required permissions?

SET @SQL = N'
EXECUTE AS USER = ''TrustedUser'';
SELECT * 
FROM dbo.uvView1 
INNER JOIN uvView2 ON uvView1.Id = uvView2.Id'

EXEC sp_executesql @SQL

Mattingly

Good suggestion I will see if I can do this. – Senter

-1: requires IMPERSONATE permission on TrustedUser which in effect means the user has all the permissions of the trusted user, since it can impersonate him at will. – Soteriology

Answer 3

No. Is there any way you can change it to not use dynamic SQL?

Selvage

Nope...not that I know of. The query is actually pivoting some data and the columns that it pivots on are dynamic. My example doesn't really show the complexity of the query. – Senter

You might consider putting all of these views in one schema and giving them datareader access on the schema then. – Horsewoman