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

share|improve this question
1  
What have you tried? What error do you get? –  Aaron Bertrand Apr 23 '12 at 22:33
    
I have tried creating a user in each database. user in database A with dbo rights to that database and a user in database b with db_reader and deny_write then i tried to exec the sproc on database a while logged in as the user from db b to no avail. i also tried logging in as user on db b and executing the proc on A just using the 4 part name but that made no sense since i would assume user b would need the credentials for a linked login to db a that has execute permissions on the sproc in question. –  deadLock Apr 23 '12 at 22:49

1 Answer 1

up vote 4 down vote accepted

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'.
share|improve this answer
    
Worked like a charm, thank you. Just so I understand what is going on under the hood the user that is being created from the login essentially just provides a gateway so that login can be granted the right to explicitly execute things on the other database. I am assuming if you setup the account like that and didn't add any specific grants the account would not be able to do anything? –  deadLock Apr 24 '12 at 5:19
    
basically i need to make sure that DBB_Owner can do anything on DB_B, and yet can ONLY run the 2 sprocs on DB_A. The user should not be able to write to any tables on DB_A, or modify anything, should only be able to execute the sprocs and have read only access to the 2 tables the sprocs touch. –  deadLock Apr 24 '12 at 5:25
    
You may also want to look at the EXECUTE AS OWNER option for the procedures. You can explicitly deny certain things from the user but in most cases this shouldn't be necessary - adding them to the database only adds them to the public role, and unless you've granted all permissions on all objects to public, this shouldn't be a concern. msdn.microsoft.com/en-us/library/ms188354.aspx –  Aaron Bertrand Apr 24 '12 at 14:52

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.