locked
Restricting the User to View other databases in the object explorer when he logs in RRS feed

  • Question

  • Hello,

    I would like want someone to throw some light on my issue. My current Production server is a Shared server which is hosting 300 DBs. I have many users associated to various databases. My requirement here is I want the users to view only the list of the databases that they own / mapped in the object explorer when they are logged in to the SQL Server Management Studio instead of viewing the entire databases, though they don't have access. By default they are able to see the entire list of databases in the object explorer which is against my requirement.

    Is there any possible way to achieve this. Any help would be greatly appreciated !! 

    Thanks 

    Momen


    Thank you... MOMEN

    • Moved by Tom Phillips Tuesday, May 7, 2013 2:36 PM Security question
    Tuesday, May 7, 2013 1:54 PM

Answers

  • If I am using the below query, it is showing only two databases Master, tempdb. But no other Db's are available to the user though he is the DB_owner for

    Hello Momen,

    I already wrote that: The user can't "see" any databases, also not those where he has permissions for. But of course they available, the user have to connect to the database by addressing it explicit.

    Are you logged on with such a user account? Then try to switch to the require database with the USE command:

    USE [YourDatabaseName];
    GO


    Olaf Helper

    Blog Xing

    • Marked as answer by Momen Azmath Tuesday, May 7, 2013 6:43 PM
    Tuesday, May 7, 2013 2:46 PM
  • SQL Server does not offer the ability to restrict the ability to view databases to only the databases to which a login has access. This is a common request but it is difficult. The access ability/restriction is contained in each database. You have 300 databases. The connection would have to try to open all 300 to see if the user had access. This would kill performance. Keeping this information outside of the database so it could be more easily looked up is temping, but that causes a different set of problems. So for now, you can either GRANT or DENY the VIEW ANY DATABASE permission, and the login will either see all databases or will see no databases.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, May 7, 2013 3:53 PM

All replies

  • Hello Momen,

    By default every user can see every database, even if he don't have permissions to access the database; that's by default.

    See VIEW ANY DATABASE Permission; if you deny this permission, then the user don't see any database, he have to address explict the database he wants to connect to. If the user have only permissions for one database, then you can define this DB as the default database for his login.


    Olaf Helper

    Blog Xing

    Tuesday, May 7, 2013 1:58 PM
  • Thanks Olaf Helper for your time on my query.

    If I am using the below query, it is showing only two databases Master, tempdb. But no other Db's are available to the user though he is the DB_owner for that database and also if I am pointing the default database to the specific database, it doesn't helped me.

    Thanks

    Momen


    Thank you... MOMEN

    Tuesday, May 7, 2013 2:31 PM
  • If I am using the below query, it is showing only two databases Master, tempdb. But no other Db's are available to the user though he is the DB_owner for

    Hello Momen,

    I already wrote that: The user can't "see" any databases, also not those where he has permissions for. But of course they available, the user have to connect to the database by addressing it explicit.

    Are you logged on with such a user account? Then try to switch to the require database with the USE command:

    USE [YourDatabaseName];
    GO


    Olaf Helper

    Blog Xing

    • Marked as answer by Momen Azmath Tuesday, May 7, 2013 6:43 PM
    Tuesday, May 7, 2013 2:46 PM
  • SQL Server does not offer the ability to restrict the ability to view databases to only the databases to which a login has access. This is a common request but it is difficult. The access ability/restriction is contained in each database. You have 300 databases. The connection would have to try to open all 300 to see if the user had access. This would kill performance. Keeping this information outside of the database so it could be more easily looked up is temping, but that causes a different set of problems. So for now, you can either GRANT or DENY the VIEW ANY DATABASE permission, and the login will either see all databases or will see no databases.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, May 7, 2013 3:53 PM
  • THank you !!

    Thank you... MOMEN

    Tuesday, May 7, 2013 6:43 PM
  • SQL Server does not offer the ability to restrict the ability to view databases to only the databases to which a login has access. This is a common request but it is difficult. The access ability/restriction is contained in each database.

    Or a database is made a securable. I have a Connect request for this problem since long. 139 up votes, but Microsoft still sits on their hands.

    https://connect.microsoft.com/SQLServer/feedback/details/273830/need-view-definition-permissions-per-database


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, May 7, 2013 9:51 PM