http://www.sqlservercentral.com/blogs/databaseexpertisecom/2010/05/28/permission-issue/

Printed 2014/10/20 05:13PM

Permission Issue

By Justin H-Davies, 2010/05/28

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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.