I found a post of setting up database access in the following site and I responded there and I wanted to share with you all about the problem and the solution that I came with.

http://database.ittoolbox.com/groups/technical-functional/sql-server-l/databaseloginconnection-3532715

Problem:

I have 2 database on a sql server name 1) Medical 2) Insurance. For Medical database, the Login is abc and password is 123 where as for the database Insurance login is pqr and the password is 567 When I login for Medical as abc/123 , I should able to view only Medical database (tables and SPs) other database should be restricted to view (tables and SPs)

Solution:

In order to solve this problem, we have to create login, create user, add this user to a database role and then grant execute permission to this user on all stored procedures. Following are the steps to achieve this problem.

Step1:

USE master

Go

CREATE LOGIN [abc] WITH PASSWORD=’123′, DEFAULT_DATABASE=[Medical], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER LOGIN [abc] ENABLE

Step 2:

USE [Medical]

GO

/****** Object: User [abc] ******/

GO

CREATE USER [abc] FOR LOGIN [abc] WITH DEFAULT_SCHEMA=[dbo]

Add abc to db_datareader role to make this user able to see the tables of this database.

Step 3:

use [Medical]

GO

exec sp_addrolemember db_datareader, abc

In order to see the stored procedure, either you should add user abc to db_owner database role or you have to grant EXECUTE permission to this user on all stored procedures.

In SQL Server 2005, you can create DB_EXECUTER database role and you can add the this user on this role as shown below.

Script to creat DB_EXECUTOR role in SQL Server 2005.

Step 4:

– CREATE A NEW ROLE

CREATE ROLE db_executor

–GRANT EXECUTE TO THE ROLE

GRANT EXECUTE TO db_executor

Now add this user (abc) to this role.

Step 5:

use [Medical]

GO

exec sp_addrolemember db_executor, abc

Now you should be able to see your tables and stored procedures on Medical database and get restricted to Insurance database. Hope this solves your problem. Please let me know if you still have issues with this.

Thank you.

Abi