For those of you who connect to database servers where you are not in full control over all databases, or where some of your databases are ever offline (or auto-closed), the new version of Management Studio that is shipping with SQL Server 2008 is going to bring you some surprises, when you try to present a list of databases in Object Explorer.
It seems that the ability to do so is hinged upon the columns that are set up in Object Explorer Details by default. In the case I came across yesterday, the offender was "Collation." The problem is that for a database that is offline or has been auto-closed, collation comes back as NULL. Well, that's not really the problem… the real problem is that SSMS throws its hands in the air when it comes across NULL for these values, and assumes this is NULL because you don't have permission. So it throws up this error:
And then refuses to show the data for ANY database, instead of just hiding the one(s) that caused the error. And this is true whether or not you have Object Explorer Details even open (my guess here is that the contents of OED are cached behind the scenes, even when it is disabled… which I speculate may be part of the reason behind the sluggishness that many have complained about). You can read more about this in Connect #354322 and in Connect #354291.
For those of you that connect to databases that are hosted by 3rd party providers, some of which are accustomed to leaving as many databases in auto-close mode as possible, you are first going to have to highlight the databases node in Object Explorer, then switch to Object Explorer Details, right-click the column header list on the right (where it says Name, Policy Health State, etc.), and un-check the Collation option. (For those unaware of Object Explorer Details, go to View > Object Explorer Details, or hit F7.) At this point, if you refresh the Databases node in Object Explorer, you should again see all of the databases on the server, even those where you don't have access. Strangely enough, OED still shows you some other data by default, that it probably shouldn't (e.g. recovery model, last backup, owner).
I have been pushing for the deprecation of AutoClose for a while (see Connect #238888), but so far it hasn't gained any traction.
A twist on this that I think is an important problem is that if you add a column to the Object Explorer Details view, say Data Space Used, if you fail to meet permissions requirements on even one database in the list, you get the same error shown above, and NONE of the data is displayed for any database at all. Couldn't they just put N/A or leave the value blank, for the database(s) where you are not allowed to see this information? I don't think they have really thought this solution through, since they are ignoring an entire market of people who use shared hosting for SQL Server. If I have five databases on a host and there are twenty others, I should be able to see the data space usage for MY databases. I posted a Connect item about this of course, too, and would appreciate your support in making it more visible: