October 11, 2012 at 9:58 am
Scenario:
1. I create the following stored procedure:
Create Proc PROCA @parm1 varchar (30)
as
Select * from TABLE1
Go
2. I create a new SQL user and assign it the public role to the database where PROCA resides.
3. I grant Exec on PROCA to the new SQL user.
When I run "Select * from TABLE1" as that SQL user, I get a SELECT permission denied message.
When I execute PROCA as that user, it runs successfully and returns the results.
I thought "Execute as Caller" meant that the stored procedure and its statements were executed by the user that executes the stored procedure. This is obviously incorrect because I should be getting the SELECT permission denied when running PROCA as well. Whats going on here?
Thanks!
October 11, 2012 at 12:20 pm
By default, SQL server grants all rights that are referenced within a stored procedure to who ever you give execute rights to. Based on my testing, you cannot override this behavior by using the 'execute as caller' clause as touched on by Greg Robidoux in this article...
From MSDN:
"How the Database Engine evaluates permissions on the objects that are referenced in the module depends on the ownership chain that exists between calling objects and referenced objects. In earlier versions of SQL Server, ownership chaining was the only method available to avoid having to grant the calling user access to all referenced objects.
Ownership chaining has the following limitations:
Applies only to DML statements: SELECT, INSERT, UPDATE, and DELETE.
The owners of the calling and the called objects must be the same.
Does not apply to dynamic queries inside the module."
October 11, 2012 at 1:15 pm
Ok that makes sense via ownership chaining.
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply