Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Location
    California
    Posts
    18

    Unanswered: MSSQL 2005: how to "hide" databases?

    For MSSQL 2005, how do you hide the databases so mssql db user will not see the database that he does not have access to when he logs in?

    I can't seem to figure this out. I want to use this approach to increase the login time (to reduce loading speed on the database list).

    Thank you Thank you...

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, you can't in 2000...

    That many database...betcha it's time for a clean up effort
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by alucarrd
    to reduce loading speed on the database list
    Can you say "non-scalable schema"? Sure you can...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Brett Kaiser
    Well, you can't in 2000...
    Are you sure you can't? I know of several drop down lists in several applications which are limited to listing only those databases that the authenticated user has access to:

    ODBC connection configurator
    Replication subscriber database picker
    Query Analyzer
    ...

    Maybe I misinterpreted your comment...

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Aug 2003
    Location
    California
    Posts
    18

    Actualy, there's a store procedure you can call...

    There's a store procedure you can call (edit) in master database so who ever logs in with Enterprise manager (sql server 2000) can only see the databases he/she has access to.

    I have done it before and I think I still have the bookmark to this article (from Microsoft).

    That's why I am trying to find out the method for MSSQL 2005.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why would a stored procedure affect Enterprise Manager? Either it affects the connections, or it is an option within Enterprise Manager.

    I haven't heard of such a thing.

    As far as the dropdowns go, it is of course possible to write custom code that accesses the system tables and returns a list of only those databases the user has access to. But this in no way "limits" what the user can see, only what is displayed in that particular control.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Aug 2003
    Location
    California
    Posts
    18
    Perhaps I just need to re-word my approach. The store procedure I referred to is called sp_MSdbuseraccess, which is what Enterprise Manager uses to determined the accessibility of each database. By modifying it, you can change it so EM only check the accessibilities for user who has permission to instead of checking all databases. This works for mssql 2000. I am looking for similar solution that works for mssql 2005.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hmm...I'll take a look at sp_MSdbuseraccess tomorrow morning, but if by "modifying it" you mean changing the code of a supplied system stored procedure, that is something I would not recommend doing.

    I'll take a look at the 2005 documentation as well.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Aug 2003
    Location
    California
    Posts
    18

    How to modify system store procedure in sql 2005?

    I looked at the article again. It looks like the real problem I am having is how to modify supplied system store procedure in sql 2005. Because it's giving me the error saying I do not have permission to make the change.

    I think that's the error i am having.

    Here is the article if you want to take a look at. This is for sql 2000.

    http://support.microsoft.com/default.aspx/kb/889696

Posting Permissions

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