Do I need GRANT for INSERT to table If has EXECUTION GRANT for procedure?

Question

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?

Answer 1

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.