SQL Server - How to grant SELECT permission on View that access data in schemas from differents owners?

Question

I need help to deal with a problem about permissions in my database.

This is the scenario:

1 Database

4 Schemas:

• 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?

Answer 1

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 tableA

• the 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 tableA

• to userX was granted SELECT permission in the created function - not EXECUTE 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();

Answer 2

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.