2

I have five domain users and five roles in my database. GRANT EXECUTE adds to the appropriate roles. Roles are included in other roles, i.e. at most we have one role that has grants for two procedures, below two more roles that have access to four and three procedures, and finally we have two roles. With this solution, if I give the GRANT EXECUTE permission to the highest role, all roles will have access to this procedure.

My question: Let's say the procedure has UPDATE and INSERT operations. Is it enough to GRANT EXECUTE to a given procedure for a given role so that the user can use this procedure and actually do UPDATE and INSERT on the table, or maybe apart from GRANT EXECUTE I need to grant the role UPDATE and INSERT to all tables within the given procedure?

1 Answer 1

4

Permissions on objects used by the stored procedure are not checked with an unbroken ownership chain. Consequently, there is no need to grant permissions on objects used by stored procedures when all objects involved have the same owner, which is typically the schema owner. Users need only stored procedure execute permissions in your case, which includes permissions inherited via role membership.

This assumes no dynamic SQL within stored procedures and objects reside in the same database. See the authorization and permissions documentation for details.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.