Understanding Cross Database Ownership Chaining in SQL Server

Problem

I have a grasp on ownership chaining, but I’m wondering what cross-database ownership chaining is and how it works? How is the owner determined across databases if ownership is based on database users?

Solution

Cross database ownership chaining is an extension of ownership chaining, except it does cross the database boundary. If you’re not familiar with ownership chaining, you should probably start with this earlier tip Ownership chaining in SQL Server security feature or security risk. An example where cross database ownership chaining might occur is if you have a view in one database which references a table in another database. The view in the first database refers to a table in the second database. If we were talking objects within the same database, if both the table and view were owned by the same user, an ownership chain would form where an end user would only need access to the view. With cross database ownership chaining, that same thing is possible, except across databases.

Cross database ownership chaining can be turned on at either the server or the database level. If cross database ownership chaining is on at the server level, it is on for all database on that server, regardless of what the individual database settings are. By default, cross database ownership chaining is turned off at the server level and it is off on all databases except the following:

• master
• msdb
• tempdb

These three system databases require cross-database ownership chaining to be turned on. Outside of these three, the general rules, due to security implications, are:

• Cross database ownership chaining should not be turned on at the server level.
• It should only be turned on for databases which require it.

You can determine if it is on at the server level with the following query (SQL Server 2005/2008).  If the value is 0, it is off at the server level. A value of 1 indicates it is on.

SELECT [name], value  
FROM [sys].configurations 
WHERE [name] = 'cross db ownership chaining'; 

This also applies for the is_db_chaining_on column in sys.databases. We can query sys.databases to see what databases cross database ownership chaining is turned on explicitly:

SELECT [name] AS [Database], [is_db_chaining_on] 
FROM [sys].databases 
ORDER BY [name]; 

For databases which are activated for cross database ownership chaining, an ownership chain is permitted to cross the database layer. The way the ownership chain is determined is similar to an ownership chain within a database. The exception is that the owner of each object is ultimately mapped to a login, if that is possible (it must be for a cross database ownership chain to form).

In SQL Server 2005 and above, it is possible to create a database user that does not map to a login. To determine these mappings, the following query shows the ultimate owner at the login level for stored procedures and user tables:

SELECT   
    so.[name] AS [Object]  
  , sch.[name] AS [Schema]  
  , USER_NAME(COALESCE(so.[principal_id], sch.[principal_id])) AS [OwnerUserName] 
  , sp.NAME AS [OwnerLoginName] 
  , so.type_desc AS [ObjectType]  
FROM sys.objects so  
  JOIN sys.schemas sch  
    ON so.[schema_id] = sch.[schema_id]  
  JOIN [sys].database_principals dp 
    ON dp.[principal_id] = COALESCE(so.[principal_id], sch.[principal_id]) 
  LEFT JOIN [master].[sys].[server_principals] sp 
    ON dp.sid = sp.sid 
WHERE so.[type] IN ('U', 'P');  

Therefore, if you have an object in one database which refers to an object in a second database, both databases are configured for database ownership chaining (or it is configured at the server level), and both objects have the same owner, then a cross-database ownership chain will form. Just as with a normal ownership chain, security is checked on the first object, but not on the second. There is one catch, however, which causes it to be different from a normal ownership chain. The login querying the first object must also have access into the second database. This could be through the guest user as it is with master, msdb, or tempdb. But if the login does not have the ability to connect to that second database,  the query will fail.

The following table shows the various options (assuming both databases are configured for cross database ownership chaining):

If a cross database ownership cannot form, then if one object refers to an object in a different database, the login must map to a user in each database which has the appropriate rights on the objects.

Here is an example to help illustrate this.

USE MASTER;
GO 
-- Database Setup
CREATE DATABASE Original_Database;
GO 
CREATE DATABASE Chained_Database;
GO 
USE Chained_Database;
GO 
EXEC sp_changedbowner 'sa';
GO 
CREATE TABLE dbo.ATable (TableID INT);
GO
USE Original_Database;
GO 
EXEC sp_changedbowner 'sa';
GO 
EXEC sp_addrole 'All_Users';
GO 
CREATE PROC dbo.QueryATable
AS
BEGIN
  SELECT TableID FROM Chained_Database.dbo.ATable;
END;
GO 
GRANT EXECUTE ON dbo.QueryATable TO All_Users;
GO 
-- Create login to use
EXEC sp_addlogin 'Standard_Login', 'SomeStrongP4ssword!';
GO 
USE Original_Database;
GO 
EXEC sp_grantdbaccess 'Standard_Login';
GO 
EXEC sp_addrolemember @membername = 'Standard_Login', @rolename = 'All_Users';
GO 
USE Chained_Database;
GO 
EXEC sp_grantdbaccess 'Standard_Login';
GO 

-- Attempt to use QueryATable without Cross-Database Ownership Chaining
-- For SQL Server 2000, log in as the created login and attempt to execute the stored procedure
USE Original_Database;
GO 
EXECUTE AS LOGIN = 'Standard_Login';
GO 
EXEC dbo.QueryATable;
GO 
REVERT;
GO

-- Turn on Cross-Database Ownership Chaining
EXEC sp_dboption 'Original_Database', 'db_chaining', 'true';
GO
EXEC sp_dboption 'Chained_Database', 'db_chaining', 'true';
GO
-- Attempt to use QueryATable after Cross-Database Ownership Chaining has been enabled
-- For SQL Server 2000, log in as the created login and attempt to execute the stored procedure
USE Original_Database;
GO 
EXECUTE AS LOGIN = 'Standard_Login';
GO 
EXEC dbo.QueryATable;
GO 
REVERT;
GO
-- Clean-up
USE MASTER;
GO 
DROP DATABASE Original_Database;
GO 
DROP DATABASE Chained_Database;
GO
EXEC sp_droplogin 'Standard_Login';
GO 

Next Steps

• Understanding the security features in SQL Server is vital to ensure your data stays intact and your confidential data is not seen by people that should not have access.  Take the time to learn more about server and database security.
  • Review all of the tips about security