Cross-Database Ownership Chaining: What a db_owner Can Reach
A reporting database has a view that counts rows in a payroll table in another database. The application login can select from the view. It has no permission on the payroll table, and the query still works. An auditor asked how, and the answer was cross-database ownership chaining.
Both databases have it turned on, and both are owned by the same login. Someone may have set that up for one report. It also lets a db_owner of the reporting database read the payroll table, as long as they have a user in the payroll database, or guest is enabled there. I built that setup on two test instances to see what a db_owner can and can’t do with it.
How an Ownership Chain Works
When a view or procedure references another object, SQL Server checks who owns each one. If the owners are the same, it doesn’t check the caller’s permissions on the referenced object.[1] The caller needs permission on the view or procedure, and that’s all. This is what makes the “grant EXECUTE on procedures, nothing on tables” model work, which I wrote about in The Case for Business Logic in Stored Procedures.
The same page lists limits. It says ownership chaining applies only to SELECT, INSERT, UPDATE and DELETE.[1] Dynamic SQL breaks the chain, and permissions are checked against the caller again. That applies to cross-database chains too.[2]
Inside one database, the two owners are often both dbo. Across databases, the comparison is between the owners’ security identifiers (SIDs), which my tests below show. The dbo user in a database maps to the SID of the login that owns the database. So two databases owned by sa have dbo users with the same SID, and objects in their dbo schemas can chain to each other. If SIDs are new to you, Logins, SIDs, and Kerberos from First Principles, Part 1: What a SID Actually Is covers them.
Where It Gets Turned On
The server option cross db ownership chaining was 0 on both of my instances. When it’s 1, every user database takes part in cross-database chains, whatever its own setting says.[3] When it’s 0, each database decides with ALTER DATABASE [database_name] SET DB_CHAINING ON. A database with DB_CHAINING ON can be the source or the target of a chain.[3] The per-database value is in sys.databases.is_db_chaining_on.[4]
Microsoft recommends leaving the server option at 0 and turning chaining on only for the databases that need it.[5] The same page warns that a database user with elevated privileges can use chaining to escalate permissions, and potentially gain sysadmin.[5]
On both of my instances, master, msdb and tempdb had is_db_chaining_on = 1, and model had 0. The documentation says DB_CHAINING can’t be set on master, model or tempdb.[3] msdb isn’t in that list, but I left it alone, because Agent and other features may depend on it.
The Test
I used SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer), both on one workstation. The server option was 0 on both, and I didn’t change it. Every result below was the same on both versions, except the job count in the msdb test.
The setup:
sat_odc_aholdsdbo.payroll, three rows of names and salaries.sat_odc_bholdsdbo.v_headcount, a view that counts the rows insat_odc_a.dbo.payroll.- Both databases are owned by
sa. - The SQL login
sat_odc_useris a user in both databases. Its only permission isSELECTon the view.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE [sat_odc_b]; GO CREATE VIEW [dbo].[v_headcount] AS SELECT COUNT_BIG(1) AS [headcount] FROM [sat_odc_a].[dbo].[payroll]; GO CREATE USER [sat_odc_user] FOR LOGIN [sat_odc_user]; GRANT SELECT ON [dbo].[v_headcount] TO [sat_odc_user]; GO |
I ran each test from a sysadmin session with EXECUTE AS LOGIN = N'sat_odc_user', wrapped in TRY...CATCH, and changed DB_CHAINING between tests. My test harness sent each statement through EXEC (). That doesn’t matter here, because the view is the first link in the chain. The setup above is an excerpt. The tables and logins were created first.
| Test | sat_odc_a | sat_odc_b | Statement | Result |
|---|---|---|---|---|
| 1 | OFF | OFF | Select from the view | Msg 229, SELECT denied on payroll |
| 2 | ON | OFF | Select from the view | Msg 229 |
| 3 | OFF | ON | Select from the view | Msg 229 |
| 4 | ON | ON | Select from the view | Returns 3 |
| 5 | ON | ON | Select from payroll directly |
Msg 229 |
| 6 | ON | ON | Select from the view, user dropped from sat_odc_a |
Msg 916, can’t access sat_odc_a |
| 7 | ON | ON | Select from the view, sat_odc_a owned by sat_odc_owner |
Msg 229 |
Both databases have to be on, and the chain covers only access through the view. Test 6 shows the caller still needs a user in the target database, or guest has to be enabled there. In test 7 I changed the owner of sat_odc_a to a second login, sat_odc_owner. The two dbo users then had different SIDs, and the chain broke.
What a db_owner Can Do With It
Next I added sat_odc_user to db_owner in sat_odc_b. sat_odc_b was still owned by sa, both databases had chaining on, and sat_odc_a was owned by sat_odc_owner.
A db_owner can’t change the setting. ALTER DATABASE [sat_odc_b] SET DB_CHAINING ON failed with Msg 15247, “User does not have permission to perform this action.” The option needs ALTER ANY DATABASE permission.[3] So someone with more rights has to turn chaining on first. After that, the db_owner has two routes.
Objects owned by dbo. A view a db_owner creates in the dbo schema is owned by dbo, and dbo maps to sa. sat_odc_user couldn’t read msdb.dbo.sysjobs directly (Msg 229). On my instances, only TargetServersRole has SELECT on that table, and the guest user has access to msdb. So sat_odc_user created this view in sat_odc_b:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE [sat_odc_b]; GO CREATE VIEW [dbo].[v_jobs] AS SELECT [name] FROM [msdb].[dbo].[sysjobs]; GO SELECT COUNT_BIG(1) AS [jobs] FROM [dbo].[v_jobs]; |
With sat_odc_b at DB_CHAINING OFF, the select failed with Msg 229. With it on, the select returned 46 on SQL Server 2019 and 26 on SQL Server 2025, the number of Agent jobs on each instance when I ran it. msdb didn’t need any change, because its chaining was already on. The same route reaches dbo objects in any database that has chaining on, is owned by the same login, and has a user or guest for the caller.
Objects owned by another login. The second route works when the owners differ. A db_owner can create a user for another login, and then create a schema that user owns. Objects in that schema are owned by that login’s SID. This is what sat_odc_user ran in sat_odc_b:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
USE [sat_odc_b]; GO CREATE USER [sat_odc_shadow] FOR LOGIN [sat_odc_owner]; GO CREATE SCHEMA [shadow] AUTHORIZATION [sat_odc_shadow]; GO CREATE VIEW [shadow].[v_payroll] AS SELECT [employee_name] , [salary] FROM [sat_odc_a].[dbo].[payroll]; GO SELECT [employee_name] , [salary] FROM [shadow].[v_payroll]; |
All three salaries came back. sat_odc_user never had a permission on payroll, and sat_odc_a is owned by a different login than sat_odc_b. With sat_odc_b set back to DB_CHAINING OFF, the same select failed with Msg 229.
sa itself is blocked as a target of this route. CREATE USER [sat_odc_sa] FOR LOGIN [sa] failed with Msg 15405, “Cannot use the special principal ‘sa’.” The block doesn’t affect the dbo route above, which already reaches sa-owned objects.
The owners of the three views, from sys.objects and OBJECTPROPERTY(..., 'OwnerId'):
|
1 2 3 4 5 |
schema_name view_name owner_user owner_login ----------- ----------- -------------- ------------- dbo v_headcount dbo sa dbo v_jobs dbo sa shadow v_payroll sat_odc_shadow sat_odc_owner |
I read data only. I didn’t try for sysadmin, which Microsoft warns chaining can lead to.[5]
Finding It Across a Fleet
This query lists every database where chaining is in effect, and counts how many of those share an owner. A chaining database whose owner also owns master and msdb (usually sa) can chain to their dbo objects.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
USE [master]; WITH [db] AS ( SELECT [d].[name] , [d].[owner_sid] , [d].[is_db_chaining_on] , [d].[is_trustworthy_on] , CASE WHEN [d].[is_db_chaining_on] = 1 THEN 1 WHEN CONVERT(int, [c].[value_in_use]) = 1 AND [d].[database_id] > 4 THEN 1 ELSE 0 END AS [chaining_effective] , CONVERT(int, [c].[value_in_use]) AS [server_option_in_use] FROM [sys].[databases] AS [d] CROSS JOIN [sys].[configurations] AS [c] WHERE [c].[name] = N'cross db ownership chaining' ) SELECT CONVERT(nvarchar(128), SERVERPROPERTY('ServerName')) AS [server_name] , [db].[server_option_in_use] , [db].[name] AS [database_name] , [db].[is_db_chaining_on] , [db].[chaining_effective] , SUSER_SNAME([db].[owner_sid]) AS [owner_login] , SUM([db].[chaining_effective]) OVER (PARTITION BY [db].[owner_sid]) AS [chaining_dbs_same_owner] , [db].[is_trustworthy_on] FROM [db] WHERE [db].[chaining_effective] = 1 ORDER BY [owner_login] , [database_name]; |
The test for a database_id above 4 follows the documentation, which says the server option applies to user databases.[3] I saved the query as detect-chaining.sql and ran it with sqlcmd against a list of instances:
|
1 2 3 4 5 6 7 8 |
$servers = @('SQLPROD01', 'SQLPROD02\SALES', 'SQLDEV01') foreach ($server in $servers) { & sqlcmd -S $server -E -C -W -s '|' -i '.\detect-chaining.sql' if ($LASTEXITCODE -ne 0) { Write-Warning "${server}: sqlcmd exit code $LASTEXITCODE" } } |
This is the output from the SQL Server 2025 instance with both test databases set to DB_CHAINING ON. I changed the instance name.
|
1 2 3 4 5 6 7 |
server_name|server_option_in_use|database_name|is_db_chaining_on|chaining_effective|owner_login|chaining_dbs_same_owner|is_trustworthy_on -----------|--------------------|-------------|-----------------|------------------|-----------|-----------------------|----------------- SQLDEV01|0|master|1|1|sa|4|0 SQLDEV01|0|msdb|1|1|sa|4|1 SQLDEV01|0|sat_odc_b|1|1|sa|4|0 SQLDEV01|0|tempdb|1|1|sa|4|0 SQLDEV01|0|sat_odc_a|1|1|sat_odc_owner|1|0 |
On my instances, with no user databases in the chain, it returned only the three system databases. For each extra row, I’d ask why chaining is on, and list who can create objects in that database. This lists db_owner members and principals with CONTROL on the database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
USE [sat_odc_b]; SELECT DB_NAME() AS [database_name] , [m].[name] AS [principal_name] , [m].[type_desc] AS [principal_type] , N'db_owner member' AS [source] FROM [sys].[database_role_members] AS [rm] INNER JOIN [sys].[database_principals] AS [r] ON [r].[principal_id] = [rm].[role_principal_id] INNER JOIN [sys].[database_principals] AS [m] ON [m].[principal_id] = [rm].[member_principal_id] WHERE [r].[name] = N'db_owner' AND [m].[name] <> N'dbo' UNION ALL SELECT DB_NAME() , [g].[name] , [g].[type_desc] , N'CONTROL on database' FROM [sys].[database_permissions] AS [p] INNER JOIN [sys].[database_principals] AS [g] ON [g].[principal_id] = [p].[grantee_principal_id] WHERE [p].[class] = 0 AND [p].[permission_name] = N'CONTROL' AND [p].[state] IN ('G', 'W'); |
I only tested db_owner. Other roles and permissions, such as db_ddladmin or ALTER ANY USER, may give part of the same reach. The query also returns a nested role’s name, not its members. So the list may be incomplete. Mapping Permission Sprawl with AI covers tracing effective permissions in more depth.
Check again after you attach a database. Detaching or attaching a database turns its chaining off.[6] An application that depended on it may stop working after an attach, and someone may turn the server option on to fix it.
What I’d Do About It
Leave the server option at 0, as Microsoft recommends.[5] The TRUSTWORTHY page says the same about the server option: leave it off unless the databases are deployed as a single unit.[7]
If two databases need chaining, limit db_owner in both to people you’d also trust with the data in every database they can chain to. Don’t let those databases share an owner with master and msdb. In test 7, a different owner broke the chain. That suggests a dedicated, low-privilege owner would break the dbo route to msdb too, though I didn’t test that against msdb itself. dbo - Database Owner shows how to set one up. The second route, through a user mapped to another login, still works for whichever login owns the target.
For a single procedure that needs data from another database, Microsoft describes an alternative. Sign the procedure with a certificate that exists in both databases, and grant permissions to a user created from that certificate.[2] That also works with dynamic SQL, which chaining doesn’t.
Microsoft advises testing applications before turning the server option off on a production server.[5] I’d do the same for a single database. If a report or job breaks afterward, you’ve probably found why chaining was on.
Some limits of this test:
- I tested two builds on one workstation, with SQL logins and
EXECUTE AS LOGIN. I didn’t test Windows logins, contained users or Azure SQL Managed Instance. - I tested views. I didn’t test procedures, functions or triggers as the first link of a cross-database chain.
- I didn’t test
db_ddladmin,db_securityadminor individual permissions. - I didn’t try any route from
msdbormasterto sysadmin.
Related: Auditing Linked Server Security: Who Connects as Whom (available 2026-11-08).
If you’ve found chaining turned on somewhere, or know why it was turned on, I’d like to hear about it. Find me on Bluesky or LinkedIn.
References
- EXECUTE AS Clause (Transact-SQL) - Microsoft Learn. States that when the calling and called objects have the same owner, no permissions are checked on the underlying objects, and that ownership chaining applies only to DML statements. ↩
- Writing secure dynamic SQL in SQL Server - Microsoft Learn. States that dynamic SQL breaks the ownership chain, that cross-database ownership chaining does not work with dynamic SQL, and that a procedure signed with a certificate present in both databases is the alternative. ↩
- ALTER DATABASE SET Options (Transact-SQL) - Microsoft Learn. Describes DB_CHAINING (source or target of a chain), states that the server option set to 1 makes all user databases participate regardless of this option, that DB_CHAINING can’t be set on master, model or tempdb, and that setting it requires ALTER ANY DATABASE permission. ↩
- sys.databases (Transact-SQL) - Microsoft Learn. Documents the is_db_chaining_on column. ↩
- Server Configuration: cross db ownership chaining - Microsoft Learn. The server option, the recommendation to leave it off and enable chaining per database, the advice to test applications before turning it off, and the warning that a privileged database user can escalate permissions and potentially gain sysadmin. ↩
- Database detach and attach (SQL Server) - Microsoft Learn. States that attach and detach operations both disable cross-database ownership chaining for the database. ↩
- TRUSTWORTHY Database Property - Microsoft Learn. Recommends keeping the cross db ownership chaining setting off unless multiple databases are deployed as a single unit. ↩