Cross-Database Ownership Chaining in SQL Server: A Double-Edged Sword

Cross-database ownership chaining is a feature in Microsoft SQL Server that allows stored procedures, views, and triggers to access objects across databases. While it can be a powerful tool, it also has potential security implications if not used correctly.

What is Cross-Database Ownership Chaining?

In SQL Server, an ownership chain is created when one object (like a stored procedure) accesses another object (like a table) in the same database. If both objects have the same owner, SQL Server only checks permissions for the first object, not the second. This is known as ownership chaining.

Cross-database ownership chaining extends this concept across databases. If a stored procedure in Database A accesses a table in Database B, and both objects have the same owner, SQL Server will not check permissions for the table in Database B.

The Risks of Cross-Database Ownership Chaining

While cross-database ownership chaining can simplify permission management, it can also create security risks:

• Elevation of Privileges: If a user has permission to execute a stored procedure that modifies a table, they can indirectly modify that table even if they don’t have direct permission to do so. This can lead to an unintended elevation of privileges.
• Database Isolation: Cross-database ownership chaining can undermine database isolation, a key principle in SQL Server security. If a database is compromised, the attacker could potentially access data in other databases.
• Ownership Confusion: If databases have different owners, it can be difficult to manage and understand the effects of ownership chaining. This can lead to confusion and potential security holes.

Best Practices for Using Cross-Database Ownership Chaining

Given these risks, it’s important to use cross-database ownership chaining sparingly:

• Limit its use: Only use cross-database ownership chaining when necessary. It’s often better to grant explicit permissions on a per-object basis.
• Understand ownership: Be clear about who owns what in your databases. This can help prevent unintended consequences from ownership chaining.
• Monitor and audit: Regularly monitor and audit your SQL Server instances to detect any misuse of cross-database ownership chaining.

In conclusion, while cross-database ownership chaining can be a useful feature in SQL Server, it’s a double-edged sword that must be handled with care. By understanding its risks and following best practices, you can use it effectively while maintaining a strong security posture.

I have provided a few scripts below to assist in your review of this feature in your own environments. The first script is to determine if cross-database ownership chaining is enabled at the instance or database level. There should be a very good and documented reason to have this enabled, and if not, discussions should be had to disable it – with the required testing first. A value of 0 is ideal for most environments. Keep in mind that master, msdb, and tempdb require ‘is_db_chaining_on’ to be on.

SELECT name, value_in_use FROM sys.configurations WHERE name LIKE '%chaining%'
SELECT name, is_db_chaining_on FROM sys.databases WHERE database_id > 4

This next script allows you to see what objects not owned by dbo, sa or the built-in roles, this is a slight modification of the old script by staggerlee011 here. As always, testing any changes is necessary, and as mentioned before in the database ownership post, sa is not necessarily the best option for object ownership.

/*
 
Query to Audit User Owned Objects
 
-- List all Databases owned by a user
-- List all Agent Jobs owned by a user
-- List all Packages owned by a user
-- List all Schemas owned by a user
-- List all Objects owned by a user
-- List all endpoints owned by a user
-- List all Event objects owned by a user
 
*/
IF OBJECT_ID('tempdb..#ownerTable') IS NOT NULL
    DROP TABLE #ownerTable
CREATE TABLE #ownerTable
    (
      [Issue] VARCHAR(100) ,
      [Database] VARCHAR(200) ,
      [Object] VARCHAR(200) ,
      [ObjectType] VARCHAR(200) ,
      [Owner] VARCHAR(200)
    )
 
/*
-- List all Non SA Database Owners
---------------------------------------------------------------------------------------------------------------------- */
INSERT  INTO #ownerTable
        ( [Issue] ,
          [Database] ,
          [Owner]
        )
        SELECT  'Database Owned by a User' ,
                name AS 'Name' ,
                SUSER_SNAME(owner_sid) AS 'Owner'
        FROM    sys.databases
        WHERE   SUSER_SNAME(owner_sid) <> 'sa';
 
/*
-- List all Non SA Job Owners
---------------------------------------------------------------------------------------------------------------------- */
INSERT  INTO #ownerTable
        ( [Issue] ,
          [Database] ,
          [Object] ,
          [Owner]
        )
        SELECT  'Agent Job Owned by a User' ,
                'msdb' ,
                s.name AS 'Job Name' ,
                l.name AS ' Owner'
        FROM    msdb..sysjobs s
                LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid
        WHERE   l.name <> 'sa';
 
/*
-- List all Schemas owned by Users
---------------------------------------------------------------------------------------------------------------------- */
DECLARE @DB_NameSch VARCHAR(100);
DECLARE @CommandSch NVARCHAR(MAX);
DECLARE database_cursor CURSOR
FOR
    SELECT  name
    FROM    sys.databases
    WHERE   state_desc = 'ONLINE'
            AND user_access_desc = 'MULTI_USER';
 
OPEN database_cursor;
 
FETCH NEXT FROM database_cursor INTO @DB_NameSch;
 
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT  @CommandSch = 'USE [' + @DB_NameSch + '] SELECT ' + ''''
                + 'Schema Owned by a User'  + '''' + ' AS [Issue], 
						' + '''' + @DB_NameSch + ''''
                + ', s.name AS [Object], ''Schema'' AS [ObjectType],  u.name AS [Owner] FROM sys.schemas s INNER JOIN sys.sysusers u ON u.uid = s.principal_id 
				WHERE s.name <> u.name
					AND u.name not in (''dbo'')';
       -- PRINT @CommandSch
        INSERT  INTO #ownerTable
                ( [Issue] ,
                  [Database] ,
                  [Object] ,
				  [ObjectType],
                  [Owner]
                )
                EXEC sp_executesql @CommandSch;
 
        FETCH NEXT FROM database_cursor INTO @DB_NameSch;
    END;
 
CLOSE database_cursor;
DEALLOCATE database_cursor;
 
/*
-- Objects Owned by User
---------------------------------------------------------------------------------------------------------------------- */
 
DECLARE @DB_NameObj VARCHAR(100)
DECLARE @CommandObj NVARCHAR(MAX)
DECLARE database_cursor CURSOR
FOR
    SELECT  name
    FROM    sys.databases
    WHERE state_desc = 'ONLINE' AND user_access_desc = 'MULTI_USER'
 
OPEN database_cursor
 
FETCH NEXT FROM database_cursor INTO @DB_NameObj
 
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT  @CommandObj = 'USE [' + @DB_NameObj
                + '] ;
WITH    objects_cte
          AS ( SELECT   o.name ,
                        o.type_desc ,
                        CASE WHEN o.principal_id IS NULL THEN s.principal_id
                             ELSE o.principal_id
                        END AS principal_id
               FROM     sys.objects o
                        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
               WHERE    o.is_ms_shipped = 0
                        AND o.type IN ( ''U'', ''FN'', ''FS'', ''FT'', ''IF'', ''P'', ''PC'',
                                        ''TA'', ''TF'', ''TR'', ''V'' )
             )
    SELECT  ''Object Owned by User'',
    DB_NAME() AS ''Database'',
    cte.name AS ''Object'',
    cte.type_desc AS ''ObjectType'',
    dp.name AS ''Owner''
    FROM    objects_cte cte
            INNER JOIN sys.database_principals dp ON cte.principal_id = dp.principal_id
    WHERE   dp.name NOT IN ( ''dbo'', ''cdc'');'
        -- PRINT @CommandObj
        -- List all Non SA Package Owners
        INSERT  INTO #ownerTable
                ( [Issue] ,
                  [Database] ,
                  [Object] ,
                  [ObjectType] ,
                  [Owner]
                )
                EXEC sp_executesql @CommandObj
 
        FETCH NEXT FROM database_cursor INTO @DB_NameObj
    END
 
CLOSE database_cursor
DEALLOCATE database_cursor


/*
-- EndPoints Owned by User
---------------------------------------------------------------------------------------------------------------------- */
INSERT  INTO #ownerTable
        ( [Issue] ,
          [Database] ,
          [Object] ,
          [ObjectType] ,
          [Owner]
        )
        SELECT  'Mirroring Endpoint owned by user' ,
                'Server' ,
                name ,
                'EndPoint' ,
                SUSER_NAME(principal_id)
        FROM    sys.endpoints
        WHERE   SUSER_NAME(principal_id) <> 'sa';


/*
-- Show results
---------------------------------------------------------------------------------------------------------------------- */
SELECT  *
FROM    #ownerTable;
 
-- drop table
DROP TABLE #ownerTable;

This last script, modified but original from Martin Smith here,  is to help with auditing the stored procedures which point to another database, each should be reviewed regularly, especially if you are using cross-database ownership chaining in your environment:

IF OBJECT_ID('tempdb..#crossdbprocedures') IS NOT NULL
    DROP TABLE #crossdbprocedures

CREATE TABLE #crossdbprocedures (
    DatabaseName NVARCHAR(256)
    , schema_name NVARCHAR (256)
	, object_name NVARCHAR(256)
	, referenced_database_name NVARCHAR(256)
	, referenced_entity_name NVARCHAR(256)
    )

 INSERT #crossdbprocedures
EXEC sp_msforeachdb '
USE [?];
SELECT	DB_NAME() as databasename
		,	OBJECT_SCHEMA_NAME(referencing_id) as schema_name
		,	OBJECT_NAME(referencing_id) as object_name
		,	referenced_database_name
		,	referenced_entity_name
FROM   sys.sql_expression_dependencies
WHERE  referenced_database_name <> DB_NAME() /*Not NULL and not current DB*/
       AND OBJECTPROPERTY(referencing_id, ''IsProcedure'') = 1 
	   AND DB_NAME() NOT IN (''master'',''model'',''msdb'',''tempdb'')
'
SELECT * FROM #crossdbprocedures