SQL Server Performance

Hiding Database from Other Users?

Discussion in 'General DBA Questions' started by sonnysingh, Oct 17, 2006.

  1. sonnysingh Member

    Hi Folks

    How can I hide one of the database in Enterprise Manager OR I would say If I like to make unavailable the specific Folders under SQL Server Registration e.g 'Security', 'Management' or specific database to specific users???

    Thanks in Advance

  2. Chappy New Member

    Theres no way to do this (that I know of anyway).
    EM is for admins, and arguably non admins shouldnt be using EM anyway.

    Handle this not by hiding the database, but by using SQL's built in security to simply restrict access to the database, and only have access.

    Then make sure the users you want to restrict do not have sa password, and are connecting to the server using a restricted login
  3. ghemant Moderator

  4. Adriaan New Member

    EM is nothing more than a client application ...

    Check the registration details of the SQL Server instance: it may say it is using Windows authentication, or it is using a SQL Server login.

    Whichever method is used, EM can only show databases for which this registered login has access permission. The same goes for objects inside the database.

    So make sure people register the SQL Server instance with their own credentials.
  5. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Whichever method is used, EM can only show databases for which this registered login has access permission. The same goes for objects inside the database.<br /><br />So make sure people register the SQL Server instance with their own credentials.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hm, is this really correct?<br />I always thought, that almost everyone who can connect to SQL Server, can also issue a <br />SELECT * FROM master.sysdatabases, which is basically what happens when EM is preparing the list of databases on a registered server. The regulating factor however is the access to that databases. <br /><br />Actually, I think EM should only be installed for people for know what they are doing. And this is not the "common" user. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  6. sonnysingh Member

    Thanks guys...
    Ok.. in this case could we create roles and assign permssions to these roles in a such way that specific users do not have access to specific folders in Enterprise Manager??

    Regards
  7. FrankKalis Moderator

  8. sonnysingh Member

    Thanks Frank... But I think you have answered for my this following Topic in same thread.



    so I am back to same place can we hide the EM's options some way or by using third party tool?????

  9. FrankKalis Moderator

    Sorry, I'm not aware of something like this.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  10. gk_sql New Member

    Sonny,

    Try the following
    1) Create a login using EM
    2) Dont give any Server Roles
    3) In Database Aceess Tab Select the databases and give only dbowner role

    Register the server using sql authentication and give the new login.
    U can see only specified databases and jobs , and other things will be hidden.

    GK
  11. Adriaan New Member

    quote:Originally posted by FrankKalis


    quote:Originally posted by Adriaan

    Whichever method is used, EM can only show databases for which this registered login has access permission. The same goes for objects inside the database.

    So make sure people register the SQL Server instance with their own credentials.
    Hm, is this really correct?
    I always thought, that almost everyone who can connect to SQL Server, can also issue a
    SELECT * FROM master.sysdatabases, which is basically what happens when EM is preparing the list of databases on a registered server. The regulating factor however is the access to that databases.
    Hm - vague memories playing tricks on me.

    One thing I do remember clearly: sp_who will fail when executed with a login that does not have access to all databases.
  12. xiebo2010cx Member

  13. satya Moderator

    USing windows authentication mode is the best option for such security threat, and I think it will have a better option to stop such unprecedented used to access the system where you can control the access via the group based level. As explained there is no such tweak on EM you can use to hide the database, rather you need to control the access using role based.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  14. sonnysingh Member

    Hi Folks

    I need to back to this topic... I have created login called 'Test', database access to 'Test' and no Sever Roles assigned to it. Authentication mode id Mixed mode.

    Now.. from one of client I have tried to connect the database using this user 'Test'. user can access to following databases...
    1. Test
    2. Master (read only)
    3. Northwind (full)
    4. Pubs (full)
    5. msdb (only see object but no access)
    6. model (only see object but no access)

    I want user 'Test' see only database 'Test' that I have assigned to the user.

    please help..
    Thanks,
    sonny

  15. Adriaan New Member

    Northwind and Pubs are example databases supplied by Microsoft for training purposes, so you can delete them safely.

    Master, Msdb and Model are system databases, so you can't really avoid logins to have read-only access to them. As long as the login is not assigned admin permissions, the default permissions should be safe.

    If you have a client app, where you want to show available databases on the server, then just make sure that the client app filters out the three system databases.
  16. satya Moderator

    In SQL 2000 it is not possible to hide such information, only in SQL 2005 it is possible and at the moment you are out of options to avoid such things.

    If you have tightened the database access then you need not worry about system databases.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  17. sonnysingh Member

    Thanks guys....

    Adriaan...how client app filter out the system database??(from 'edit SQL Server Registeed properties' option only?)

    quote:
    If you have a client app, where you want to show available databases on the server, then just make sure that the client app filters out the three system databases.

    Satya... you are right and you have advised same before in this thread (last post by you). But again there must be any way to achieve this... I can understand that you can delete sample databases or hide them. Alternatively create user(login) which won't have admin rights but again user still can browse through it.. don't you think it would be more secure that if users wouldn't have the option at all that they do not need to??

    Please advice..
    sonny
  18. ghemant Moderator

    Hi,
    in the client application filter it by querying master..sysdatabases system table. Yes while registering / editing information in SQL Server's register property you can hide system database from their by uncheking "show system databases and system objects" but this is a trick , user can revert back it.
    If you are giving access to EM / Client tool to thoses users who are "Enthu" they will always play with it but as said earlier default permission is a safe for you. And implement Windows Authentication with proper Application Roles as said by Satya.

    Regards

    Hemantgiri S. Goswami
    MS SQL Server MVP
    -------------------------
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami

    http://hemantgirisgoswami.blogspot.com
  19. satya Moderator

    Sonny
    I can understand your furstation for this feature, but it is too late now for SQL 2000 as there will not be any fixes or service packs from Microsoft on this version. At the moment your option is either live with it or upgrade to SQL 2005. If the users unable to connect to those database where they don't have any privileges then why do you worry, nothing will happen.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  20. sonnysingh Member

    Thanks folks.. it is really appreciable...

Share This Page