Hide SQL DB's Users do Not have Access to

rr-4098 1,641 Reputation points
2024-04-10T15:43:00.6166667+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,162 questions
{count} votes

Accepted answer
  1. LucyChen-MSFT 5,060 Reputation points Microsoft Vendor
    2024-04-17T09:54:46.8633333+00:00

    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: User's image Please don't forget change it into partial:User's image
    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'
         
      
      2
    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';
         
      
      3
    6. Use SSMS to connect: You can enter the database name manually!!!
    7. 4
    0 comments No comments

7 additional answers

Sort by: Most helpful
  1. Guoxiong 8,206 Reputation points
    2024-04-10T16:42:04.5066667+00:00

    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.

    0 comments No comments

  2. Olaf Helper 45,366 Reputation points
    2024-04-10T17:17:42.98+00:00

    "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.

    0 comments No comments

  3. rr-4098 1,641 Reputation points
    2024-04-10T17:21:54.25+00:00

    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?

    0 comments No comments

  4. Guoxiong 8,206 Reputation points
    2024-04-10T17:49:06.09+00:00

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

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

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.