Hide SQL DB's Users do Not have Access to

Question

I know I can set the permission to Deny "View all DB's" but I need users who are mapped to their DB's to be able to see them in SSMS. Thoughts?

Answer 1

Hi @rr-4098,

Do you mean how to change the exiting database into contained database? I did the test below:

1. This is my database named aaa: Please don't forget change it into partial:
2. Create a new login with password:

      create login TestC with password='test'
      
   

3. Create a new user for the new login:

      use aaa
      Create user LUCY FOR login TestC with Default_schema=[dbo]
      
   

4. You can check it:

      select * from master.sys.server_principals where name = 'TestC'
      
   

      select * from aaa.sys.database_principals where name= 'LUCY'
      
   

   
5. Change into contained database:

      Use aaa
      GO
      sp_migrate_user_to_contained
          @username = N'LUCY',
          @rename = N'keep_name',
          @disablelogin = N'do_not_disable_login';
      
   

   
6. Use SSMS to connect: You can enter the database name manually!!!
7. 

Answer 2

In the Login Properties, you can map the user to the database(s) they should access to. For example, if you have three databases A, B and C, you want the user1 only to access to B, in the User Mapping of the Login Properties, you check the database B for that user and uncheck the other databases.

Answer 3

"View all DB's" is an "all or nothing"

They still can change the context to an other database, but they "see" only the current database.

Answer 4

The test user account already had DB mapping. What is odd is they can see the Master and TempDB's. They cannot see their DB but can run queries against it. They need to see the DB since they do not know all the DB names. Is their any way to hide other DB names?

Answer 5

Make sure the user has the right to view the databases:

USE [master];  
GO  
GRANT VIEW ANY DATABASE TO [TestUser];
GO