Sean Lange (6/26/2015)
alex.sqldba (6/26/2015)
Hi Guys,If I granted execute right to Schema1 for sp MyProc;
And MyProc updates something in Schema2
Will it work? Or will it fail as its a different Schema?
Cheers
Alex
If the user has permission to execute the procedure then it is assumed that anything that procedure does is acceptable. Procedure calls do NOT check permissions on every object being referenced internally. That would be a huge performance waste of time.
That only holds up when the referenced objects have the same owner as the called object *.
If Schema1 and Schema2 have the same owner then it will work fine * since permission checks would be bypassed but if the schemas have different owners then permissions are again checked and the caller must have explicit permissions on called object. A new ownership chain can again be started in that scenario.
To the OP, this would let you move across schemas no problem maintaining your ownership chain in the process, however this relegates the scheam to a classification container and not much of a security container any longer:
CREATE SCHEMA Schema1 AUTHORIZATION dbo;
CREATE SCHEMA Schema2 AUTHORIZATION dbo;
* Note that an object's owner can be changed to something other than the owner of the object's schema for additional security options (and confusion).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato