Cross-Database Ownership Chaining: Why It Is Off and What to Use

Crossing a database boundary can break a procedure that reads local tables successfully. Ownership chaining explains part of that difference, but enabling a broader trust relationship is not the default repair.

Understand What Ownership Chaining Skips

When an eligible module and its referenced objects share an owner, SQL Server can skip downstream object permission checks. The caller still needs permission to enter the module. That chain lets a procedure expose a controlled operation without granting direct access to every underlying table.

The chain depends on ownership, not on matching schema names alone. An object's owner can derive from its schema or an explicit ownership assignment. Review effective owners before diagnosing why the expected chain does not apply.

Crossing a database boundary adds a separate security relationship. Ordinary local access does not establish unrestricted access to another database. The caller and referenced objects also need the appropriate database access context.

I inspect the exact failing object and effective caller before changing security settings. I also check whether the intended interface is a view or a procedure. A broad permission change can conceal the original misunderstanding while creating a larger one.

Inspect Ownership Chaining Scope Without Enabling Anything

The instance configuration can permit chaining broadly, while database settings can control participation more narrowly. Keep the instance-wide option off unless an explicitly reviewed requirement justifies otherwise. Inspect both ownership chaining layers before drawing conclusions about the existing environment.

SELECT name, value AS ConfiguredValue, value_in_use AS ActiveValue
FROM sys.configurations
WHERE name = N'cross db ownership chaining';

SELECT name, is_db_chaining_on, is_trustworthy_on,
       SUSER_SNAME(owner_sid) AS DatabaseOwner
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

The default database setting for ordinary user databases does not enable cross-database chaining. System database behavior and existing application configurations require separate review. Record the actual values rather than changing every database to match a remembered default.

Turning on the instance option affects a wider boundary than one reporting query. A principal able to create suitably owned objects in one participating database can gain unintended access through the chain. Trust every database participant only after understanding that consequence.

Do not combine broad chaining with TRUSTWORTHY as an unexplained permission fix. Those settings describe different security mechanisms. Preserve the narrow interface and diagnose which permission the actual operation requires.

Choose a Narrow Alternative Before Building the Bridge

Directly granting SELECT on one target object is a simple alternative when direct reads are permitted. A target view can expose approved columns and rows. Those designs require the appropriate target database user and an explicit access decision.

When callers should use only a specific procedure, module signing can add the required permission during that procedure's execution. The certificate identity receives the narrow target permission. The caller retains only permission to invoke the approved interface.

The following example requires two isolated test databases, AppSourceDb and AppTargetDb. Both must have database chaining disabled, with the instance-wide option off. AppSourceDb must already have a protected database master key available through the approved key-management process.

The example assumes a non-sysadmin test login named ReportCaller already exists, created through approved credential management. The C:\SqlCertificates directory must exist on the SQL Server computer with appropriate service-account access. The export file must not already exist.

USE AppTargetDb;
GO
CREATE TABLE dbo.BridgeItem
(
    ItemId int NOT NULL PRIMARY KEY,
    ItemCode varchar(20) NOT NULL
);
INSERT dbo.BridgeItem(ItemId, ItemCode) VALUES (1, 'A100');
CREATE USER ReportCaller FOR LOGIN ReportCaller;
GRANT CONNECT TO ReportCaller;
GO
USE AppSourceDb;
GO
CREATE USER ReportCaller FOR LOGIN ReportCaller;
GRANT CONNECT TO ReportCaller;
CREATE CERTIFICATE ReadBridgeCert
    WITH SUBJECT = N'Narrow read access for the approved bridge procedure';
CREATE USER ReadBridgeSigner FROM CERTIFICATE ReadBridgeCert;
BACKUP CERTIFICATE ReadBridgeCert
    TO FILE = 'C:\SqlCertificates\ReadBridgeCert.cer';
GO

Only the public certificate is exported for the target database. Keep the signing private key protected in the source deployment process. No private key is required in the target merely to recognize the matching certificate identity.

Sign the Procedure and Grant the Certificate Identity

The procedure references a specific target table and returns explicit approved columns. It does not accept an arbitrary target database or SQL text. That fixed interface defines what the additional permission is intended to authorize.

USE AppSourceDb;
GO
CREATE OR ALTER PROCEDURE dbo.ReadBridgeData
    @ItemId int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ItemId, ItemCode
    FROM AppTargetDb.dbo.BridgeItem
    WHERE ItemId = @ItemId;
END;
GO
ADD SIGNATURE TO OBJECT::dbo.ReadBridgeData
    BY CERTIFICATE ReadBridgeCert;
GRANT EXECUTE ON OBJECT::dbo.ReadBridgeData TO ReportCaller;
GO
USE AppTargetDb;
GO
CREATE CERTIFICATE ReadBridgeCert
    FROM FILE = 'C:\SqlCertificates\ReadBridgeCert.cer';
CREATE USER ReadBridgeReader FROM CERTIFICATE ReadBridgeCert;
GRANT SELECT ON OBJECT::dbo.BridgeItem TO ReadBridgeReader;
GO

The certificate user in the target receives SELECT on the named table only. The application caller does not receive that direct grant. Signing adds the certificate's authorized identity during execution of the signed module.

Do not add an explicit DENY to the caller merely to demonstrate lack of direct access. That denial can conflict with permissions supplied through module signing. Lack of a direct grant already establishes the intended test in this isolated setup.

Review dynamic SQL and nested module calls separately when extending the procedure. Signing does not authorize arbitrary objects beyond the granted permission. Nested modules can require additional signing or countersigning to preserve the intended execution relationship.

Test the Caller and the Denied Direct Path

Test with the existing non-sysadmin login, rather than relying on an administrator's successful execution. An administrator already has access that can hide missing signature or user configuration. The demonstration explicitly reverts its temporary impersonation context.

USE AppSourceDb;
GO
EXECUTE AS LOGIN = N'ReportCaller';
BEGIN TRY
    EXEC dbo.ReadBridgeData @ItemId = 1;
    BEGIN TRY
        SELECT ItemId, ItemCode
        FROM AppTargetDb.dbo.BridgeItem;
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS DirectReadError,
               ERROR_MESSAGE() AS DirectReadMessage;
    END CATCH;
    REVERT;
END TRY
BEGIN CATCH
    REVERT;
    THROW;
END CATCH;

The approved procedure should work while the direct read lacks permission. Verify both outcomes rather than treating any returned row as proof of correct security. Unexpected direct access requires reviewing roles, grants, ownership, and inherited privileges.

Confirm that neither broad chaining nor TRUSTWORTHY supplied an unintended alternate route. Record the caller, target permission, certificate identity, and module signature. That evidence distinguishes a working narrow design from a working accidental privilege.

Preserve the Signature Through Deployment and Recovery

Altering a signed module removes its signature. Deployment must therefore re-sign the final reviewed definition and verify the result. A procedure change followed by an unexplained permission failure deserves a signature check before a broad grant.

USE AppSourceDb;
GO
SELECT OBJECT_SCHEMA_NAME(cp.major_id) AS SchemaName,
       OBJECT_NAME(cp.major_id) AS ModuleName,
       c.name AS CertificateName, cp.crypt_type_desc
FROM sys.crypt_properties AS cp
JOIN sys.certificates AS c ON c.thumbprint = cp.thumbprint
WHERE cp.class = 1
  AND cp.major_id = OBJECT_ID(N'dbo.ReadBridgeData');

On my test copy, this query returned one SIGNATURE BY CERTIFICATE row. After a plain ALTER of the procedure, it returned nothing until the procedure was signed again.

Does the same caller still have only the approved access after deployment or restoration? Test the signed procedure and the direct-read boundary again. Preserve source signing keys, target certificates, and login mapping through the supported recovery process.

Cross-database ownership chaining needs an explicit trust decision when retained for an existing requirement. A narrow signed procedure instead expresses a specific additional permission through a reviewed interface. Choose the boundary the application actually requires and verify it with the intended caller.

Related reading on this blog: Understanding Grant, Deny, and Revoke Permissions and Auditing Who Holds CONTROL SERVER and IMPERSONATE Rights.

A working cross-database query is not a justified trust boundary, it is access that still needs a narrow and verified permission design.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.