How to fix issue - The server principal is not able to access the database under the current security context.

Question

I have a Data Factory (DF) and SQL Managed Instance (MI) with 2 databases "dbx" and "dby".

Using script activity, my DF executes my stored procedure (SP) written in X.

When SP performs a merge from dbx.Table1 to dby.Table1 --> Failure (Authorization issue as stated in question)

When SP performs a merge from dbx.Table1 to dbx.Table2 --> Success

When SP performs a merge from dby.Table1 to dby.Table2 --> Success

My authorization setup:

Using a system-managed identity, DF is assigned as SQL Managed Instance Contributor to MI

Inside both dbx and dby, I executed these commands:

CREATE USER [DF] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [DF];
ALTER ROLE db_datawriter ADD MEMBER [DF];
GRANT SELECT TO [DF];
GRANT INSERT, UPDATE, DELETE TO [DF];
GRANT EXECUTE TO [DF];
GRANT CREATE TABLE TO [DF];
GRANT ALTER TO [DF];

Hope you can help me. Thank you in advance.

Answer 1

Would you be willing to try if enabling cross database ownership on MI solves the issue?

EXEC sp_configure 'cross db ownership chaining', 1;
RECONFIGURE;