Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Understanding Cross Database Ownership Chaining in SQL Server


By:   |   Read Comments (5)   |   Related Tips: More > Security

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):

Access to 1st DB Access to 2nd DB Guest User Enabled on 2nd DB? Cross Database Ownership Forms?
Yes No No No
Yes No Yes Yes
Yes Yes No Yes
Yes Yes Yes Yes

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



Last Update:






About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, April 02, 2015 - 2:10:10 AM - sonali Back To Top

how can perform cross database query for 12 tables but one server on sql server
 


Tuesday, March 31, 2015 - 10:26:38 AM - Marcio Andrey Oliveira Back To Top

I just want to say thank you. This post was extremely useful.

 


Tuesday, November 11, 2014 - 7:32:25 PM - Eric in Sacramento Back To Top

I don't get it.


Thursday, March 07, 2013 - 4:46:28 PM - MBurns_08109 Back To Top

In your first article on ownership chaining you gave this example:

CREATE USER TestUser WITHOUT LOGIN;
GO

CREATE TABLE Marketing.OwnedTable (
  TableValue INT
);
GO

ALTER AUTHORIZATION ON Marketing.OwnedTable TO TestUser;
GO

I have a case where I want such a user to execute a stored Proc across a databse boundary.

In short: DB-A.dbo.MyStoredProc  is used to query DB-B.dbo.Sometable

How can/should the cross-database (same server instance) ownership chaining work for such a user when the user is defined in either DB-A or DB-B with no corresponding server login id? From what I've read, the trustworthy setting should be involved, but that says that it only applies to users defined in the local database (DB-A, or D-B respectively), and not across them? Does it help to define the TestUser login in both databases? I'm not at all sure how to proceed to make this work.


Monday, October 17, 2011 - 8:05:55 AM - Copello Back To Top

 

sy does not work!


Learn more about SQL Server tools