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

Hi @rr-4098,

Thanks for your information.

Here is my database for test.

I checked the database properties, it has my domain\user account as the owner, because I created it.

Then I created a Test account with login to allow me to connect into SQL Server (Using SQL Authentication and the Test login)

During I created the account, I Use the server level login (Test) to set its Mappings to include the Test database.

Here you can see what my Test sees at the server level (everything) while it throws an error if I try to access any database.

However, when Test tries to access a database that it is mapped to, it can see the objects inside that database that have been granted to it under database user's Securable permissions.

Please feel free to share the issue more detailed here If you have any other questions, thank you.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 3

Thank you for the feedback. I did the same steps as you. The user can only access the DB they have access to and get errors on the other DB's. The problem is we do not want to user the "see" the other DB's. Please note, when I deny "view all db's" they can still query the DB, but need to see it since they are not sure of all the table names.

Answer 4

If a user is now a owner of a DB, doesn't this mean they have full rights to the DB? If this is the case this will be a problem.