SQL Server – Cross Database Ownership and Security Context Issue

permissionsSecuritysql serversql-server-2016stored-procedures

I have this script that set a bunch of databases to the same db owner, and set cross database ownership on at database level

select database_name=name,
       db_owner_name=suser_sname(owner_sid),
       db_owner_sid=owner_sid,
       is_db_chaining_on,
       SQL='USE ' + QUOTENAME(name) + char(10) + 'EXEC sp_changedbowner ''MY_COMPANY\sqldbslaveowner'';'  + CHAR(10),
       CROSS_DB_OWNERSHIP_SQL='USE MASTER' + CHAR(10) + 'ALTER DATABASE ' + QUOTENAME(name) + SPACE(1) + 'SET DB_CHAINING ON;' + CHAR(10)
from sys.databases 
where name in('apcore','junocore','APIA_Repl_Sub','JUNOCore_Repl_Pub','JunoFinance')

It sets the db owner to a login, that has no permissions associated with it – my_company\sqldbslaveowner. It is an AD account.
and the following:

USE MASTER ALTER DATABASE [JUNOCORE] SET DB_CHAINING ON; 
USE MASTER ALTER DATABASE [JUNOFinance] SET DB_CHAINING ON; 
USE MASTER ALTER DATABASE [apcore] SET DB_CHAINING ON; 
USE MASTER ALTER DATABASE [JUNOCore_Repl_Pub] SET DB_CHAINING ON; 
USE MASTER ALTER DATABASE [apia_repl_sub] SET DB_CHAINING ON;

so, after done that, when I run the below I am sorted:

select database_name=name,
       db_owner_name=suser_sname(owner_sid),
       db_owner_sid=owner_sid,
       is_db_chaining_on
from sys.databases 
where name in('apcore','junocore','APIA_Repl_Sub','JUNOCore_Repl_Pub','JunoFinance')

enter image description here

This was all good, and working fine, then yesterday, for no apparent reason, it broke up, and I can't figure it out the bit that is missing.

What I am experiencing is the following (it is all within the databases shown above):

in one of the databases junofinance I go ahead and create a stored procedure that selects data from a table in another database called JunoCore. It should all be good since we are under cross database ownership.

Note that the procedure has execute as owner and the owner is the same guy on both databases.

I grant permission to execute the procedure to one of my application logins.

use JUNOFinance
go
create proc prc2  
with execute as owner  
as  
begin  
select * from junocore.[dbo].[agency]  
end  
go
grant execute on prc2 to [juno]
go

Now when I execute this procedure I get the following error:

use JUNOFinance
go
execute as login='juno'

exec prc2

The server principal "My_Company\sqldbslaveowner" is not able to
access the database "JUNOCORE" under the current security context.

enter image description here

This was working fine.

I will have to check on the creating certificates as it was all working before!

The databases are part of an availability group

Only msdb is set to trustworthy

As I said it was all working, the only thing I have done lately is to upgrade sql server 2016 to SP2, but I did not have any issues with this server in particular.

what else can be suggested that I do to fix this scenario?

Best Answer

The server principal "My_Company\sqldbslaveowner" is not able to access the database "JUNOCORE" under the current security context.

You should not need to EXECUTE AS OWNER in this scenario, and it will break as you can't cross databases when impersonating a database principal. Impersonation and Ownership Chains are two separate mechanisms for delegation.

And the normal thing that people forget is that the end user must have database access to the target database. Cross Database Ownership chains don't give a user access to a database, they only suppress permissions checks on objects in that database.

Here's a simple repro:

/*
revert
use master
drop database a
drop database b
drop login appuser 
*/
go
create database a
create database b

ALTER DATABASE a SET DB_CHAINING ON; 
ALTER DATABASE b SET DB_CHAINING ON; 
go

use b
go
create procedure foo
as
begin
  select 1 a;
end

go

use a
go
create procedure foo
as
begin
  exec b.dbo.foo;
end

go

create login appuser with password='P@Jhs9d!$12'
create user appuser for login appuser
grant exec on foo to appuser

go
execute as login='appuser'
  exec foo;
--fails
--Msg 916, Level 14, State 1, Procedure foo, Line 4 [Batch Start Line 37]
--The server principal "appuser" is not able to access the database "b" under the current security context.

go
revert

go
use b
create user appuser for login appuser
deny select,insert,update,execute to appuser --not strictly necessary
go
use a
execute as login='appuser'
  exec foo;
revert
--works

go

--`execute as owner` is incompatible with cross database ownership chains
create or alter procedure foo
with execute as owner
as
begin
  exec b.dbo.foo;
end
go

use a
execute as login='appuser'
  exec foo;
revert
--fails
--Msg 916, Level 14, State 1, Procedure foo, Line 5 [Batch Start Line 69]
--The server principal "NORTHAMERICA\dbrowne" is not able to access the database "b" under the current security context.
Related Question