11

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?

A-K
  • 16,804
  • 8
  • 54
  • 74
Dismissile
  • 32,564
  • 38
  • 174
  • 263
  • The user is accessing the view, right? I don't think security cares how they access it. – JNK Nov 02 '10 at 19:26
  • 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. – Dismissile Nov 02 '10 at 19:52
  • As to why this happens I found [Dynamic SQL and Ownership Chaining in SQL Server @ mssqltips.com](https://www.mssqltips.com/sqlservertip/1822/dynamic-sql-and-ownership-chaining-in-sql-server/) 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"_ – Nickolay Feb 27 '19 at 09:17

3 Answers3

11

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • How about a solution using a database trigger to monitoring the Sql of the user and reject it if the SQL start with `select`? – ca9163d9 Jan 26 '17 at 19:16
  • 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`) – Nickolay Feb 27 '19 at 11:33
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
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
0

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

Vidar Nordnes
  • 1,334
  • 10
  • 20
  • 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. – Dismissile Nov 02 '10 at 19:30
  • 3
    You might consider putting all of these views in one schema and giving them datareader access on the schema then. – K Richard Nov 02 '10 at 19:35