Executing a stored procedure on remote database

Question

I have 2 databases on the same server. DB-A and DB-B. DB-B has a user that has read and write access to database B. I would like to grant the user in DB-B execute permissions to 2 stored procedures in database A. I was able to do this when the databases were on seperate servers by setting up a linked server, and mapping a linked login. I am having trouble with this when the databases are on the same server. Can someone please tell me how I can accomplish this task.

Thanks, James

Answer 1

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'.