SQL Server - How to grant SELECT permission on View that access data in schemas from differents owners?
Based on one of the suggestions that Dan Guzman gave me in your answer, and after read this question I found a workaround to my problem.
Apparently, there is no way, in scenario as described, to grant to userX SELECT
permission in the viewABC without grant also SELECT permission on tableA.
The solution that I found was the following:
in schemaD I created a Table-Valued Function that return the same record set of viewABC - despite ownerX isn't owner of schemaA, he has
SELECT
permission on tableAthe
EXECUTE AS
clause was used to guarantee that any execution of the function will use ownerX permissions - in this context doesn't matter the permission that userX has on tableAto userX was granted
SELECT
permission in the created function - notEXECUTE
permission, since the function return a table
The Table-Valued Function sample code:
CREATE FUNCTION schemaD.udfABC ()
RETURNS @tabABC TABLE (
fieldA INT NOT NULL, fieldB INT NOT NULL, fieldC INT NOT NULL
)
WITH EXECUTE AS OWNER
AS
BEGIN
INSERT INTO @tabABC (fieldA, fieldB, fieldC)
SELECT a.fieldA, b.fieldB, c.fieldC
FROM schemaA.tableA a
INNER JOIN schemaB.tableB b ON a.id = b.idA
INNER JOIN schemaC.tableC c ON b.id = c.idB;
RETURN;
END
Granting SELECT
permission:
GRANT SELECT ON schemaD.udfABC TO userX;
Now userX can use this command to get data:
SELECT * FROM schemaD.udfABC();
I can't think of a way to avoid granting SELECT
permissions due to the broken ownership chain in the view's underlying objects. One solution is to change the owner of TableA to ownerX so that the view ownership chain is unbroken:
ALTER AUTHORIZATION ON OBJECT::dbo.tableA TO ownerX;
Note that this will give ownerX CONTROL
permissions on dbo.tableA. If that is not acceptable, you'll need to use a module instead (e.g. table-valued function) and sign it with a certificate based on a user that has the needed object SELECT
permissions.