I need help to deal with a problem about permissions in my database.
This is the scenario:
- schemaA; owner dbo
- schemaB; owner ownerX
- schemaC; owner ownerX
- schemaD; owner ownerX
In schemaD I have a view named viewABC, that gets information from tables and views allocated on schemas schemaA, schemaB e schemaC.
A user userX have
SELECT permission for viewABC.
With the following command I grant permission:
GRANT SELECT ON schemaD.viewABC TO userX;
When userX try to execute a
SELECT against the view, this way:
SELECT * FROM schemaD.viewABC;
We get the error:
The SELECT permission was denied on object 'tableA', database 'MyDatabase', schema 'schemaA'.
I do understand that the error happens because tableA is on a different schema, with a different owner (dbo instead ownerX), and SQLServer applies userX permissions to grant or deny access. Once userX don´t have explicit access to tableA the query execution returns error.
How to solve this without grant SELECT permission on tableA for userX?