1

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

  • which database? Sounds like SQL Server... – kevinsky Nov 24 '13 at 14:08
  • 1
    Is it only this context that the proc should be executed as ub? The EXECUTE AS can help if that's the case – billinkc Nov 24 '13 at 14:17
  • @kevinsky it is SQL Server, sorry for not noting that... – Alex Pulver Nov 24 '13 at 16:59
  • @billinkc, if I understand your question correctly - the proc which is currently running under user "ua" can also run under user "ub". – Alex Pulver Nov 24 '13 at 17:02
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.

  • 1
    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! – Alex Pulver Nov 24 '13 at 17:09
  • in both cases the procedure dbo.b_someproc lives in database a and is a pass-through wrapper to call b.dbo.someproc. – Sebastian Meine Nov 24 '13 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.