sql-serversql-server-2008-r2database-security

SQL Server 2008 R2 : restrict server login from seeing all but 2 databases on server


On my server that is hosting SQL Server 2008 R2, I open SSMS and under Security -> Logins there is a login named "SomeLoginName". When I log in to the server with this login, I am able to see all of the databases on the server.

I would like to restrict this user to only see 2 of the databases that are on the server. I've seen some solutions that say to revoke the VIEW ANY DATABASE permission for the login and then add the login as the db_owner for the databases I want "SomeLoginName" to be able to see. I don't want to have "SomeLoginName" as the db_owner for the databases that it is supposed to see.

Is there a way that I can configure "SomeLoginName" to only see 2 databases on the server without "SomeLoginName" being the db_owner for these 2 databases?

Thanks in advance.


Solution

  • Thanks to @sepupic , his/her answer is correct. It turns out that I actually am running MS SQL Server 2012 so I was able to implement the Contained Database concept. The steps listed on the linked pages in @sepupic 's answer didn't work for me though. I found this one and put this script together. Here's what it does:

    1. Changes the 'contained database authentication' to 1 for the MS SQL Server instance
    2. Runs RECONFIGURE
    3. Creates a contained database
    4. Creates a user for the database

    Here's the script:

    USE master;
    
    GO;
    
    EXEC sp_configure 'contained database authentication', 1;
    
    GO;
    
    RECONFIGURE;
    
    GO;
    
    CREATE DATABASE ContainedDB2
    
    CONTAINMENT = PARTIAL;
    
    GO;
    
    USE ContainedDB2;
    
    GO;
    
    CREATE USER cduser2
    
    WITH PASSWORD = N'Pa$$word',
    
    DEFAULT_SCHEMA = dbo;
    
    GO;
    

    Then you just configure the connection to the contained database in the section that begins with

    Login and Verify the User Permissions on a Contained Database

    Using the script I put together and configuring the connection under the section I mentioned sets it up so you connect to the server with the user that is created and that user can only see the contained database(s) you want it to. You have to configure the user to have permissions like the db role db_datareader in the contained database but instructions on how to do these types of things are easy to come by if you search for them. Thanks again to @sepupic for getting me started on coming up with an answer.