SQL Server Permissions on Stored Procs with dynami

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?

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.

Answer 2

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

Answer 3

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