CODEWITHSUNDEEP
sqlsql-server
Pedro Jose
Pedro Jose

Reputation: 442

Permissions to view certain databases

I have 4 databases,

Database 1 Database 2 Database 3 Database 4

I have a client that I want to give access to my DB instance. If I give it full access it will see all 4 databases.

I just want him to be able to "see" Database 1 and Database 4 and that the other 2 do not appear

I understand that there are read permissions, and this makes it possible to see all the databases, but not the content, which each table has, but it is possible that when that user connects to my instance, they can only see Database 1 and Database 4, and that the other two cannot be seen?

Upvotes: 0

Views: 203

Answers (2)

Stu
Stu

Reputation: 32579

Try the following - deny viewing any database to the specific login, they can still view DBs they are owner of:

Use master
go
Deny view any database to Login
go
Alter authorization on database::Login to Login
go

Upvotes: 0

GuidoG
GuidoG

Reputation: 12014

Make a Login for that user

in SSMS in the Object Explorer go to Security/Logins then right mouse "new login"
In the login properties go to User Mapping and select for each database if and what he is allowed there
Any database that is "unticked" there he will not see, and if he would see it in some way he will not be able to connect

enter image description here

Upvotes: 1

Related Questions