Grant full rights to only 1 database & not anything on other databases

Hi Friends,

I have a sql instance which has 5 databases.

I have one user requesting access to ONLY 1 database out of all 5.

Idea is to grant full rights to user to only 1 database & while all other dtabases user cann't even see.

It is also okay if user can't even access SSMS.
LVL 10
Minesh ShahSharePoint & all about itAsked:
Who is Participating?
 
Minesh ShahSharePoint & all about itAuthor Commented:
can be done, get the user in security of sql server with say 'public' access & add ur db in server roles.
then go to db & verify the particular user added above have owner permissions.
Done.
When this user access the db via ssms, he/she can view other db names but when clicked on any other access denied pop up appears.

Also,

Verification can be done using this script:

USE [master]
GO
CREATE LOGIN [Test] WITH PASSWORD=N'abc', DEFAULT_DATABASE=[DEV], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DEV]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [DEV]
GO
ALTER USER [test] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [DEV]
GO
EXEC sp_addrolemember N'db_owner', N'test'
GO
use [master]
GO
DENY VIEW ANY DATABASE TO [test]
GO


Now...The SQL Login 'test' would have FULL RIGHTS on DEV Database and cannot view/query any other userDB's.
0
 
OCDanCommented:
In SSMS  then Security (under databases)
Right click the logins folder and pick New Login
Choose either WindowsLogin (From AD)/SQL Server
Set the default database to whichever you want them to have access
Then Go to UserMapping - check map on the DB needed
Select what rights you want to give them on that DB
-(all of them will make it simpler for you)

Done
0
 
Minesh ShahSharePoint & all about itAuthor Commented:
Thanks.
One thing always confuses me:
Does above 1 also requires to grant minimum permissions on SSMS> SEcurity section as well?
0
 
OCDanCommented:
I generally do give read only access as habit,  so I can't say whether its needed or not
0
 
Minesh ShahSharePoint & all about itAuthor Commented:
ok
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.