This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(259.20000000000005, 70%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EC1%3C/text%3E%3C/svg%3E)
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.
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;
Hola Alberto! Thank you for responding. I'll try and I will let you know. At the moment, I don't have access to perform RECONFIGURE so I'm asking auth team now to grant me. One moment please :)
I would not expect that to have any effect. And if it has - turn it off anyway. There is a good reason why cross-db ownership chaining is turned off by default - there are security risks with turning it on.
So you are saying that the stored procedure is in X, and this still works: SP performs a merge from dby.Table1 to dby.Table2?
If you instead create an SQL login, and then create users from that SQL login with the same permissions as above, and then do:
EXECUTE AS LOGIN = 'SQLogin'
go
EXEC YourSP
go
REVERT
What happens?
How does the header of the SP look like? That is , the part up to the first AS?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(198.4, 69%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESR%3C/text%3E%3C/svg%3E)
Hi @CyrusDaguro-1527
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Hi @CyrusDaguro-1527
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Hello Everyone / Alberto,
Apologies I am offline for couple days. @Alberto Morillo 's suggestion was rejected by the admins due to risks.
Hi @Erland Sommarskog I'll try to raise this with the admins if they will approve. Here is my procedure. Thank you very much for your inputs!
The solution provided represents a better solution. You may implement that solution with contained database users also,
Thanks for the SP. I wanted to see if has an EXECUTE AS clause, but it has not.
Note that my suggestion with EXECUTE AS LOGIN is not meant as a solution, but only as a troubleshooting step. Please let us know the outcome.
Hi @CyrusDaguro-1527
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.