SQL Server Performance

Hide SQL Server Database

Discussion in 'SQL Server 2005 General DBA Questions' started by sonnysingh, Mar 21, 2008.

  1. sonnysingh Member

    Hi Folks
    One of post "How to hide databases in SQL Server Management Studio from unauthorised users?" say that

    "InSQL Server 2005 it is possible with a new server side role that hasbeen created. VIEW ANY DATABASE permission is a new, server-levelpermission. A login that is granted with this permission can seemetadata that describes all databases, regardless of whether the loginowns or can actually use a particular database. Please note By default,the VIEW ANY DATABASE permission is granted to the public role.Therefore, by default, every user that connects to an instance of SQLServer 2005 can see all databases in the instance."
    So how can we take off VIEW ANY DATABASE permission from PUBLIC role? as I want to hide some database from this user.

    Thanks in advance
    sonny

  2. melvinlusk Member

    In Management Studio, right click the server then click "Properties". Click on "Permissions" and then select the "Public" role and remove Grant from "View Any Database". I'm not sure if this will achieve what you're looking for but it's how to remove that permission.
    I'm curious as to why you're going this route. Why can't you just deny access to that database at the login level?
  3. atulmar New Member

    public role may not be available in Logins and Roles list, in that case you need to add that from server roles, then you can see properties of public role.
    By default this setting is having "view any database" enabled.
  4. sonnysingh Member

    When I create a login (user), Public role come default... how can I remove default part so pubic role won't be available for this login....
    Thanks in Advance....
  5. sonnysingh Member

    I have not find the public role so I have added it. But when I highlighted the Public role, the 'Effective Permissions' command button stay Grey (inactive). am I missing something or is there any other way I can do this?
    Thanks.....

  6. satya Moderator

    As referred above by default PUBLIC role would have this privilege and you can run following query to get more information:
    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_idWHERE permission_name = 'VIEW ANY DATABASE' ;GO
    Also you can achieve the REVOKE this by uisng TSQL
    REVOKE VIEW DATABASE STATE TO PUBLIC;
  7. sonnysingh Member

    Thanks.....


    1.
    I have tried following actions:
    REVOKE VIEW DATABASE STATE TO PUBLIC;
    and connect to the Server using Login (Test) that ofcourse have Public rights. But still can see other database existing on the Server.
    2.
    InManagement Studio, I go to server Properties and then Permissions. Select the Public role and Deny View Any Database rights. But this will only show Master and TempDB when Login (Test) connect to the database, who is db_owner on Sales database.

    3.
    What I tried to do is Hide the database from users and only database(s) visible to the user that own by the user. for example....
    There are 3 A, B, C Databases exist on the Server.
    Login (Test) is db_owner of database B. So only B visible to the Login (Test) when connect to the server (database).
    Thanks in Advance.
  8. sonnysingh Member

    Hi Folks
    This is another reason that i want to hide database from any of other user as I have installed DBA Dashboard and I do not want to see this by any of user. Please help...
    Thanks in Advance
  9. satya Moderator

    Sonny
    Is that tool created by Greg larsen?
    if so it is best to email him about your question by referring this thread to solve.
  10. harshal New Member

    Hi,
    I could make it to work using the following steps:
    1. login as sa, create user user1
    2. deny view any database to user1

    3. make user1 as db_creator.
    4.log out of sa
    5. login with user1
    6. create database test
    now only master,tempdb and test will be visible to user1.
    -Harshal.
  11. sonnysingh Member

    Thanks... Harshal, But I want to hide existing databases rather created under new user that deny view any databases rights. because there are already couple of databases on the server. So any other way to achieve this?
    Thanks...
    sonny

  12. madhuottapalam New Member

    Sonny, I tested myself and its working just fine for me Steps are as follows
    (a) Created a Login called Testpb
    CREATE LOGIN testpb WITH PASSWORD = 'test'
    (b) Connected using this login i am able to see all the databases
    (c) Revoke the VIEW ANY DATABASE permission from Public REVOKE
    VIEW ANY DATABASE FROM public
    (d) Logged in again using TestPB login and now i am not able to see any databases(e) Logged out and Granted VIEW ANY DATABASE to Public role
    (f) Logged in using TestPB login again and i am able to see all the databases
    I hope this solved the problem
    Madhu
    Grant
    VIEW ANY DATABASE TO public
  13. sonnysingh Member

    Thanks Madhu...
    I knew that it will be works but what i want is after step C, I want user Testpb able to see only database(s) on those acces given to the user rather unable to see any database. I have given access to Testpb for a database as db_owner and after revoke View any database from public role, Testpb not able see any database (of course)...
    so can we able to do it any which this in SQL Server 2005?
    Thanks again....
  14. Brar New Member

    Well, I am trying to do the same thing as sonnysingh and I havent been able to do it myself.
    I have searched it on Google but found nothing about this.
    Looks like it is not possible in SQL Server Management Studio. Might be possible in Sql Server Enterprise Manager.
    Any Geeks, can you please spare some time and tell us how to hide a particular database for a particular user in SQL Server Management Studio.
  15. Brar New Member

  16. sonnysingh Member

    Thanks Brar.... [:)]
  17. MichaelB Member

    Yeah, pitty the billionaire company! boo hoo! [:D]

Share This Page