Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 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.

share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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