How to hide all databases I am not authorised to use in Sql Server 2005 Management Studio?

Question

I have to access some customers databases being hosted in a shared environment. There are numerous databases being hosted on any given customers instance. So everytime I access a database I have to scroll and search.

1. I would like to be able to configure Management Studio to just go directly to the database I want to work with for a given connection and hide all others on that connection/instance. But i still want to be able to see databases on other instances i might be working with, i.e. local using the same Object Explorer.

2. If that not possible is there any reason why the web hosting provider would grant their customers the VIEW ANY DATABASE permission? Im assuming thats why I can see all the other dbs?

If i cant configure Management studio to do what i want as per (1) then I was going to email the provider and ask that they prevent me from seeing them from the "server" end. Would this be an easy thing for them to do? If they REVOKE the VIEW ANY DATABASE permission then that should solve my "problem" right? But would it create any others?

Answer 1

2 articles describe it (no point if I copy/paste, really)

One and Two

Answer 2

You will require access to the master db to effect the outcome you want. There are no options to configure Sql Server management Studio (SSMS) to do what you want.

So (1) is out.

(2) however is a go so long as your service provider play alongs. You are right they will have to

DENY VIEW ANY DATABASE TO youruseracount

which will require access to the master db. But before they do this they will have to set youruseraccount as the owner of your database.

sp_changedbowner 'youruseraccount'

The side affect of all of this is

a. You wont be able to see any other databases in the instance which is what you want.

b. Only one user will be able to use SSMS to admininster your database with the 'View Only My Db' list. This is because only one user can be the Database Owner.