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