I have an issue that I have not run into before. I have a database with about 5 different schemas in order to keep some tables and stored procedures separated. I created a role for each schema as an application access role for the objects in that schema. So for instance a schema called 'Common' has tables and stored procedures. The role is cgCommonMaster and has rights to execute all stored procedures within that schema. It also has SELECT rights on every table in that schema.

A second schema has the same setup as the Common schema but for the objects in that schema. Now, I have a user that is a member in both roles, so in theory it has access to execute the stored procedures and perform selects on all tables in both schemas.

I have a stored procedure in the Common schema that performs a delete first on a row from the second schema. Then another on a row in the common schema. I am getting a permission denied on the delete from the second schema call.

It would not be my desire to grant a role direct permission to do delete on a table. I only want this to be done through the stored procedures. If the role has rights to execute the stored procedure, why would I get this error and how do I avoid granting permissions to delete on a table?

I can only presume this occurs because of the cross schema call, but there must be a way around it. Any help appreciated.