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

authenticationlinked-serversql serverstored-procedures

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

Best Answer

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.

Related Solutions

Sql-server – Executing a stored procedure on remote database

You don't need to create linked logins or anything like that. For a local database you can just add the user to DB_A from the login, then grant exec to the two procedures. First:

CREATE DATABASE DB_A;
GO
CREATE DATABASE DB_B;
GO

-- create a login:
CREATE LOGIN DBB_Owner WITH PASSWORD = 'foo', 
  DEFAULT_DATABASE = DB_B,
  CHECK_POLICY = OFF;
GO

-- make DBB_Owner the DB_B owner:
-- step is not necessary, just trying to match your scenario
ALTER AUTHORIZATION ON DATABASE::DB_B TO DBB_Owner;
GO

USE DB_A;
GO

-- create a local user from the server login:
CREATE USER DBB_Owner FROM LOGIN DBB_Owner;
GO

Now create three procedures. For the first two, give explicit exec rights to DBB_Owner, and don't allow them any permissions on the third.

CREATE PROCEDURE dbo.Proc1
AS
    SELECT 'Yes! - from Proc1';
GO
CREATE PROCEDURE dbo.Proc2
AS
    SELECT 'Yes! - from Proc2';
GO
CREATE PROCEDURE dbo.Proc3
AS
    SELECT 'No! - from Proc3';
GO

GRANT EXECUTE ON dbo.Proc1 TO DBB_Owner;
GRANT EXECUTE ON dbo.Proc2 TO DBB_Owner;

Now connect to the server as DBB_Owner with the password foo, and try to execute the three procedures:

EXEC DB_A.dbo.Proc1;
EXEC DB_A.dbo.Proc2;
EXEC DB_A.dbo.Proc3;

--or

USE DB_A;
EXEC dbo.Proc1;
EXEC dbo.Proc2;
EXEC dbo.Proc3;

Results:

Yes! - from Proc1
Yes! - from Proc2
Msg 229, Level 14, State 5, Procedure Proc3, Line 1
The EXECUTE permission was denied on the object 'Proc3', database 'DB_A', schema 'dbo'.

Sql-server – How to create a stored procedure using xp_fixeddrives

I've recreated your scenario on my test server that has 2 linked servers that were created manually.

CREATE TABLE SERVERNAME
(
    [Name] nvarchar(255)
);

INSERT INTO SERVERNAME ([Name]) SELECT name from SYS.SERVERS;    
GO

CREATE PROCEDURE freespace
AS
BEGIN
    CREATE TABLE #freespace
    (
        drive VARCHAR(50)
        , freedrivespace VARCHAR(50)
    );
    DECLARE @servername VARCHAR(50);
    DECLARE @sql VARCHAR(100);
    DECLARE curspace CURSOR LOCAL FORWARD_ONLY FOR
    SELECT [Name] FROM SERVERNAME;
    OPEN curspace;
    FETCH NEXT FROM curspace INTO @servername;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = '[' + @servername + ']...xp_fixeddrives';
        INSERT INTO #freespace
        EXEC @sql;
        FETCH NEXT FROM curspace INTO @servername;
    END
    CLOSE curspace;
    DEALLOCATE curspace;
    SELECT * FROM #freespace;
END
GO

EXEC FREESPACE;

The results for my server are:

drive   freedrivespace
C       75305
I       277458
K       861878
C       24378
D       90630
F       7575392
G       972578
H       768495
I       50952
J       7150657
C       13799
D       25162
F       6745607
G       982818
H       580167
I       50881
J       7224593

Granted the results are not exactly usable since the name of the server is not shown, however it does work. This tells me the problem is not in your dynamic SQL; the problem lies with your linked server configuration.

Are you able to manually run a simple query such as:

SELECT * FROM [server1].master.sys.tables;

This should either return results, or most likely the same error you are seeing above.

Best Answer

Related Solutions

Sql-server – Executing a stored procedure on remote database

Sql-server – How to create a stored procedure using xp_fixeddrives

Related Question