SQL Management Studio Shows All Databases

Discussion in 'Databases' started by Bruce, May 22, 2008.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. Bruce

    Bruce DiscountASP.NET Staff

    Interestingly.. we had a phone call w/ the SQL team last week and we were discussing this issue. We were informed that it is a UI bug (Management studio) but as of this point there's no fix.

    Bruce

    DiscountASP.NET
    www.DiscountASP.NET
     
  2. Hand in hand with the Microsoft SQL Team, love it. [​IMG]
     
  3. Time to get your votes in.
    Jason wrote up a nice little Blog about this:
    http://weblogs.asp.net/jgaylord/archive/2008/05/21/sql-management-studio-shows-all-databases.aspx

     
  4. mpd

    mpd

    The following msdn library article explains how to easily turn off the ability to to see databases you do not have permissions for.
    http://msdn.microsoft.com/en-us/library/ms189077.aspx

    To limit visibility to database metadata, deny a login the VIEW ANY DATABASE permission. After this permission is denied, a login can see only metadata for master, tempdb, and databases that the login owns.


    SQL Server 2005 Books Online (September 2007)
    VIEW ANY DATABASE Permission

    VIEW ANY DATABASE permission is a new, server-level permission. A login that is granted this permission can see metadata that describes all databases, regardless of whether the login owns or can actually use a particular database.




    The VIEW ANY DATABASE permission regulates the exposure of metadata in the sys.databases and sys.sysdatabases views, and the sp_helpdb system stored procedure.


    By default, the VIEW ANY DATABASE permission is granted to the public role. Therefore, by default, every user that connects to an instance of SQL Server 2005 can see all databases in the instance. To verify this behavior, run the following query:



    SELECT l.name as grantee_name, p.state_desc, p.permission_name
    FROM sys.server_permissions AS p JOIN sys.server_principals AS l
    ON p.grantee_principal_id = l.principal_id
    WHERE permission_name = 'VIEW ANY DATABASE' ;
    GO


    The metadata that describes the master and tempdb databases is always visible to public.


    Members of the sysadmin fixed server role can always see all database metadata.


    Database owners can always see rows in sys.databases for databases that they own.


    Granting the CREATE DATABASE and ALTER ANY DATABASE permissions to a login confers access to the database metadata.




    [​IMG]Note:

    CREATE DATABASE permission is implicit to the dbcreator fixed server role.







    [​IMG]Note:

    If you grant CREATE DATABASE and ALTER ANY DATABASE permissions to a login and do not deny VIEW ANY DATABASE to the login, the login can see all rows in sys.databases.





    To limit visibility to database metadata, deny a login the VIEW ANY DATABASE permission. After this permission is denied, a login can see only metadata for master, tempdb, and databases that the login owns.
     
  5. Bruce

    Bruce DiscountASP.NET Staff

    Yeah.. We are aware of this technique but this is actually where the bug w/ Management studio is.


    If we do this, only the primary user (dbo right) will see the database. If you try to connect to the DB w/ an additional user (even with adequate right), they will NOT see the database BUT they can still run query directly to the database.


    In light of this, we decided that it is best to show all the database until MSFT fix the GUI bug.


    Bruce

    DiscountASP.NET
    www.DiscountASP.NET
     
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page