Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    1

    Question Unanswered: how to hide list of databases in SQL Query Analyser

    Hi SQL gurus,



    I have created a user login and when accessing that via network i am able to not only see user's database, but also database of other users in Query Analyser. Again in Sql enterprise manager it is showing databases of all users and this user is able to see the details of 'master' database.

    How can we stop him seeing other details and use and see only his database.

    Anticipating a quick response.


    Thanks,
    Tanmay

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Exclamation Re: how to hide list of databases in SQL Query Analyser

    Hi SQL gurus, I have created a user login and when accessing that via network i am able to not only see user's database, but also database of other users in Query Analyser. Again in Sql enterprise manager it is showing databases of all users and this user is able to see the details of 'master' database. How can we stop him seeing other details and use and see only his database. Anticipating a quick response. Thanks, Tanmay

    Q1 How can we stop him [from] seeing, [inserting, deleting, editing, etc.,] other [user DB] details?
    A1 Create a db role in the user DB(s) you wish to restrict access to called something like db_deny (you will probably want to do this in each DB). Make db_deny a member of the existing db_denydatareader and db_denydatawriter roles; or instead, deny the db_deny role specific IUDSE rights for specific tables views, etc., individually, as may be appropriate (whatever db_deny members need to be restricted from interacting with and or doing). Assign the logins you wish to so restrict to db_deny in each DB.

    Q2 [How can we allow him to] see [and insert, delete, edit, execute, etc.] only [in] his database?
    A2 Carefully follow the more general answer (described in A1) on a more granular scale. Caution: note carefully that logins that have all rights denyed in Master and / or MSDB would have a very difficult time with many things (connecting, using sp_help, using EM to manage jobs, etc., etc.). I suggest you learn by starting with restricting rights in user databases, (before moving onto doing so in master and msdb).

Posting Permissions

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