How to run stored procedure in another database on the same server using a different login?

Question

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!
Alex

• kevinskio: which database? Sounds like SQL Server...
• billinkc: Is it only this context that the proc should be executed as ub? The EXECUTE AS can help if that's the case
• Alex Pulver: @kevinsky it is SQL Server, sorry for not noting that...
• Alex Pulver: @billinkc, if I understand your question correctly - the proc which is currently running under user "ua" can also run under user "ub".

Answer 1

Assuming ua is associated with the login la and ub is associated with lb.

To make this work you could grant impersonation of lb to la. Then you can create a wrapper of b.dbo.someproc in a like this:

CREATE PROCEDURE dbo.b_someproc
AS
BEGIN
 EXECUTE AS LOGIN='lb';
 EXEC b.dbo.someproc;
 REVERT;
END;

However, impersonation is a very broad permission that you probably do not want to grant. To get around it you can either add user ub to database a or user ua to database b.

If you add ub to a you can write a wrapper like this:

CREATE PROCEDURE dbo.b_someproc
WITH EXECUTE AS 'ub'
AS
BEGIN
 EXEC b.dbo.someproc;
END;

By far the easiest way however is to ad ua to b and just allow it to call the procedure directly.

• Alex Pulver: I don't have control over B database permissions, so I will probably have to go with adding ub to database A. One question regarding the latter code example - did you mean to refer to dbo.a_someproc instead of dbo.b_someproc? CREATE PROCEDURE dbo.a_someproc WITH EXECUTE AS 'ub' AS BEGIN EXEC b.dbo.someproc; END; Thanks a lot!
• Sebastian Meine: in both cases the procedure dbo.b_someproc lives in database a and is a pass-through wrapper to call b.dbo.someproc.