locked
Can I hide databases from users who don't have access in SQL Management Studio? RRS feed

  • Question

  • I have a client who would like to access his database via SQL Server Management Studio. I created a login for him. This login has access only to his database on the server (I set this up using the user mapping), the Securables has Connect to SQL checked (otherwise he won't see his database).  When I test this login, he is able to see all the other databases on the server, though when he clicks on the ones he doesn't have access to, it will tell him that he can't access that database.  He can access his database alright.  My question is, can I hide all the other databases from him, and only display that only database he has access to?  I have tried many different ways, I can't seem to be able to do so.  Thanks in advance.

    Tuesday, April 21, 2015 2:40 PM

Answers

All replies

  • you can do that denying view any database permission to the user but that also  makes him not to see  his  database in the object exlporer unless he owns the database. 

    he can still access the database but cannot see it in the object explorer.

    there was thread on this recently - will link it here for reference : check Erland's reply.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/27bcf7d8-961a-427d-b2c9-121299b1aafa/how-to-create-users-in-sql-server-for-accessing-only-one-database-withour-dbowner-permission-?forum=sqlsecurity


    Hope it Helps!!



    • Edited by Stan210 Tuesday, April 21, 2015 3:15 PM
    Tuesday, April 21, 2015 2:44 PM
  • Thank you very much for the reply and your link.   I have tried Deny View Any Database.  The problem is that the user is not the owner of the database.  So he can't see anything now.  Can I add him as the owner of that particular database?  He isn't computer savy so I only want to give him read right to the database, is that possible?
    Tuesday, April 21, 2015 4:17 PM
  • Thank you very much for the reply and your link.   I have tried Deny View Any Database.  The problem is that the user is not the owner of the database.  So he can't see anything now.  Can I add him as the owner of that particular database?  He isn't computer savy so I only want to give him read right to the database, is that possible?

     you probably do not want to give db_owner permission - just to get around this issue.

    db_owner - can do anything in the database.  so, you do not do that. 

    as, Erland mentioned in that link, there is other way to do it as this time, 

    so, I would rather not give him db_owner permission than have him see all the databases,(of course he cannot access anythem-so, there is no harm). but db_owner , you can have real harm, if he does somehitng unwanted.


    Hope it Helps!!

    Tuesday, April 21, 2015 4:24 PM
  • Thanks for the insight.  I didn't realize that SQL security is restricted in so many ways.  I won't give the user owner right then.  In the past with another client, I had tried Microsoft Access, which worked perfectly for this purpose.  The client set up a ODBC connection to one particular database, and was able to see all the tables, could query all of them without being able to change a  single record.  I had recommended Microsoft Access to this current client, but he despised Access and thought SQL was cool and would rather see data "on the server" via SSMS.

    Tuesday, April 21, 2015 4:34 PM
  • As suggested in the other thread, consider the contained database concept for this.

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, April 22, 2015 11:11 AM
  • >>>When I test this login, he is able to see all the other databases 

    However, this works perfectly if the user is the owner of the database. Look 
    that …

    • Create a new SQL login "login1"
    • Create a user named “login1” in master database
    • Grant CREATE DATABASE to login1
    • While impersonating login1, create a database called “dbtest”
    • Revoke CREATE DATABASE permission from login1
    • Revoke VIEW ANY DATABASE permission from PUBLIC
    • Register this server as login1
    • From the “login1” session, expand database tree. Now, you should see 
    master, tempdb, dbtest
    • Grant VIEW ANY DATABASE to PUBLIC
    • From the “login1” session, you should see all the databases


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Monday, April 27, 2015 5:33 AM