I learned something at the NTSSUG meeting tonight, via our guest speaker, Bryan Smith (MSDN Profile), and I wanted to share it with any developers who may read this.
I was under the impression that granting a user rights, such as “execute”, to a specific Stored Procedure does not grant any rights to the objects referenced by that Stored Procedure, and this is completely true. However, I understood that to mean that a user who has “execute” on a Stored Procedure, but does not have “update” rights to one of the tables that Stored Procedure writes to, would therefore be unable to execute that Stored Procedure successfully, due to missing those underlying rights. This second conclusion, however is actually a big “it depends” and for some small database shops, it is flat-out wrong. The deciding factor has to do with Ownership Chains.
If the Ownership Chain is unbroken between the originating Stored Procedure and the objects that it references, that is to say, all of these objects have the same owner as the Stored Procedure, then the Query Execution engine does not do further rights-checks against each of the called objects. Since, in many environments, most objects are owned by “dbo”, granting “execute” on a Stored Procedure to a specific login means, quite literally, that the user in question can successfully execute that Stored Procedure, regardless of their rights on those objects.
This, obviously, means that you have to be much more judicious in assigning the “execute” permission to users and also that you should consider purposefully breaking Ownership Chains in some scenarios. However, don’t drop everything and start making changes; you wouldn’t want to break all of the possible existing code that was previously taking advantage of this. But, as you go forward, and especially as new Stored Procedures are created, be very stringent on the assigning of rights. Above all, be aware of how this affects any T-SQL code that you write.
0 Comments.