I have 2 databases (A and B) on the same server. Database A has user ua defined, and database B has user ub defined.
I have a stored procedure in A, running as ua, that needs to run a stored procedure in B, running as ub (this is the only user that has access).
Until now, A and B were located on different servers, so I used linked server to switch login. But now, when running the stored procedure in A, I get the following error: "Transaction context in use by another session".
I understood from googling for this error that this is by design, based on Microsoft's article - it mentions that loopback linked servers cannot be used in a distributed transaction.
Are there any other ways to make the stored procedure in A, running as ua, to run the stored procedure in B as ub, if both databases are located on the same server?
Thanks a lot!