SQL Server Permissions on Stored Procs with dynamic SQL

sql sql-server stored-procedures database-security
Dismissile picture Dismissile · Nov 2, 2010 · Viewed 7.4k times · Source

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

Remus Rusanu picture Remus Rusanu · Nov 2, 2010

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.

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More