Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    8

    Unanswered: How do I Hide Databases from External Users?

    I am going to give user rights for an external user to connect to my SQL Server via Client Network Utility.

    Atlough I have given user permissions to only access one database and not the whole list, how do I make sure that they cannot see all the other databases on my SQL Server?

    I have 20 instances of databases on my SQL Server and ideally I would like to give 20 different people access - but each of them when they enter my SQL Server, should not even know that the other databases exist.

    Thanks.

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: How do I Hide Databases from External Users?

    RE:
    Q1 I am going to give user rights for an external user to connect to my SQL Server via Client Network Utility.

    Q2 Although I have given user permissions to only access one database and not the whole list, how do I make sure that they cannot see all the other databases on my SQL Server?

    I have 20 instances of databases on my SQL Server and ideally I would like to give 20 different people access -

    Q3 but each of them when they enter my SQL Server, should not even know that the other databases exist. Thanks.
    A1 {What was meant is not 100% clear; however, bestowing "[user rights for an external user to connect to SQL Server]" would generally be accomplished within Enterprise Manager, *Query Analyser, osql, isql, or programmatically, i.e.(via an API: ADO, CLib, DMO, OLEDB, etc.,); but not via the Client Network Utility (at least not directly)?}

    A2 One may meet such requirements by designing the user application(s), i.e.(the ones the users will use with their respective DBs), such that other DB catalogs are not displayed. (This is arguably probably one of the better means of meeting such a requirement.)

    Note: Various "all encompassing" implementation approaches may entail significant negative consequences. For example this includes many implementations that:
    a Deny users Select and Exec rights to certain objects in the Master DB.
    b Modify / add objects to the Master DB (this option especially, is NOT recommended).
    c Require special connectivity software that supports the desired feature set (custom made ODBC, Sql Server, OLEDB, etc., drivers).

    A3 You may wish to verify that this is in fact an important requirement. (That is, one that is worth the ramifications of the means chosen to implement a design that will meet the requirement.) As noted in A2, maintainability and / or functionality may be impaired with some kinds of "DB hiding" implementation approaches.

    -----

    * The following Special Stored Procedures are supported in MS Sql Server 2k (execute from QA, etc.) for managing security:
    sp_addalias
    sp_addapprole
    sp_addgroup
    sp_addlinkedsrvlogin
    sp_addlogin
    sp_addremotelogin
    sp_addrole
    sp_addrolemember
    sp_addserver
    sp_addsrvrolemember
    sp_adduser
    sp_approlepassword
    sp_change_users_login
    sp_changedbowner
    sp_changegroup
    sp_changeobjectowner
    sp_dbfixedrolepermission
    sp_defaultdb
    sp_defaultlanguage
    sp_denylogin
    sp_dropalias
    sp_dropapprole
    sp_dropgroup
    sp_droplinkedsrvlogin
    sp_droplogin
    sp_dropremotelogin
    sp_droprole
    sp_droprolemember
    sp_dropserver
    sp_dropsrvrolemember
    sp_dropuser
    sp_grantdbaccess
    sp_grantlogin
    sp_helpdbfixedrole
    sp_helpgroup
    sp_helplinkedsrvlogin
    sp_helplogins
    sp_helpntgroup
    sp_helpremotelogin
    sp_helprole
    sp_helprolemember
    sp_helprotect
    sp_helpsrvrole
    sp_helpsrvrolemember
    sp_helpuser
    sp_password
    sp_remoteoption
    sp_revokedbaccess
    sp_revokelogin
    sp_setapprole
    sp_srvrolepermission
    sp_validatelogins
    Last edited by DBA; 02-02-03 at 01:14.

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    With what tools will your users be connecting to your Server? ODBC, OSQL, Enterprise Manager?
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Jan 2003
    Posts
    8
    Originally posted by Paul Young
    With what tools will your users be connecting to your Server? ODBC, OSQL, Enterprise Manager?

    Thank you for your posts

    I am using EM to connect

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Great, what will your users use to connect?

    If your users will use EM or Query Analyzer then they have enough to see the diffrent DBs with little to no effort. A knowlegable user can even see system tables via I/OSQL. Past that, in ODBC a use will still be able to get a list of DB's if they specify a database when setting up the DSN.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •