Ownership chaining

Ownership chaining is the means by which SQL provides access to data in a view without requiring a user to have direct access to its underlying objects.

When a user accesses a view, SQL compares the owner(s) of the object(s) it references with the owner of the view itself. If ownerships match, explicit permissions on the underlying object are not checked.

This means that I can:

• create table T;
• create view V to expose only some of T's columns and rows;
• grant access to V to a user, enabling the user to see the data it returns without granting him/her direct access to T.

This also has a slight performance benefit because less permission-checking work is required when evaluating the query.

Objects don't (directly) have owners: schemas do. The owner of a schema owns all the objects in that schema.

SELECT 
  s.name AS SchemaName
, dp.name AS [OwnerUser]
, sp.name AS [OwnerLogin]
FROM sys.schemas s
  INNER JOIN sys.database_principals dp ON dp.principal_id = s.principal_id
  LEFT JOIN sys.server_principals sp ON sp.[sid] = dp.[sid]

By default, ownership chains only exist between objects in the same database with the same owner. To allow ownership chaining across databases, you must:

1. ensure that the databases have the same owner (login);
2. ensure that the objects' schemas have the same owner;

3. enable cross-database chaining in both/all participating databases:

   ALTER DATABASE MyDatabase
   SET DB_CHAINING ON

You can check the necessary database properties as follows:

SELECT 
  d.name AS DatabaseName
, p.name AS DatabaseOwner
, d.is_db_chaining_on
FROM sys.databases d
  INNER JOIN sys.server_principals p ON p.[sid] = d.owner_sid

Cross-database ownership chaining does not work with dynamic SQL unless the user exists in all participating databases. No additional explicit permissions are required.

Members of the db_owner role in a database in which cross-database chaining is enabled can gain access to other chaining-enabled databases. Suppose I am db_owner in MyDatabase, have no access to PrivateDatabase and that both are chaining-enabled:

• if I attempt to SELECT from some table in PrivateDatabase, I am denied;
• if I create a view that performs the same select, and make its owner the same as that of my target table, the ownership chain allows me access. Anything in the dbo schema is likely owned by sa!