A little Management Studio "oops"
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.
It 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:
Thank you so much for posting this solution. It saved me countless hours of troubleshooting on my own.
Thank you so very much. I ran into this particular problem, and you saved me. Thanx a tonne.
Thanks for the Help.. it was really very helpfull.
I have one query.. Is this happen when Only SQL 2008 Client Tools is installed or when full SQL Server 2008 is installed. bcoz i a server which has sql server 2008 EE installed on one machine and when i try to login in that server and connect to the share sql server.it did not give the error It simply showed me list of all the database and some for some empty Collation column.
Can any have a reason for this…
Thank You! Thank You! Thank You!
Been pulling my hair out trying to figure out what I was doing wrong, Thank you so much, it was Microsoft not me causing the error:)
Yes! Thank you OnlyOneN – I couldn't for the life of me find the collate option!
Thanks a lot. Your article saved a lot of time today.
This post was a lifesaver! Thanks.
Very Gooooooooooooooooood thanks alooooooooooooooooooooooooooooot
Thank you thank you thank you thank you thank you.
Thanks! And people ask why I curse MS…
I almost spend whole day just to install/upgrade MS-2008 and after installing the same issue, but thanks your post really save my another one day to just fique out the solution. SQL TEAM should really need to fix this so that other will not suffer.
thanks a lot mate, you're a genius for figuring that out!
Thank you so much! I really couldnt understand why this was happening!
You're a god-send!
Kev.
this is the most helpfull page that i've ever visit on the web.
thank u.
I am glad I found this post! I have a database at GoDaddy and have been unable to use SSMS to connect to it even through it is a 2008 database! GoDaddy says that it is an issue with SSMS because they are able to get my database on that server.
I followed this simple change and I now have access to my database.
Thank you, thank you, thank you…
It is the issue of providing rights. Give proper rights to the user with which you are logging in.
Hope this helps.
Sanjay Vema
Tom, I'm not sure I follow, can you elaborate?
That works for getting a dump of the databases on the server, but how can you get access to the in the Object Explorer. 🙂
Thank you, this was a big help!
OnlyOneN's newbie instructions should be added to original post. I'm NOT a newbie, but I still spent 15 minutes trying to find Object Explorer Details before resorting to reading the rest of the posts.
Thanks again!
Thanks! It works!
Thanks! I was totally stuck!
Thanks for the solution, had me baffled, however I too am having the same issues as several people here when I try to "Generate scripts" I am getting the same error. Any clue how to fix that one?
Cheers
Thnaks for this. You saved me hours of investigation and head scratching!
Thank you so much. I was trying to connect to SQL2005 host database through SQL2008 Management Studio, and i wasn't able to list the databases with the same error mentioned.
After hiding the column 'Collation' in the object explorer details, it works.
1. View–>Object Explorer Details (Or F7)
2. Right click on the column header and select Collation to be in unchecked state.
Thanks
Thank you fixed for fixing the nagging GoDaddy issue!
Thanks Aaron and OnlyOneN! You solved my connection problems! You'd think MS would *try* their software against a hosted database at least once? I mean the intended audience of this Express sofware is clearly not the enterprise crowd with massive local databases. It's guys/gals that run remote websites on shared servers. I mean this latest incarnation of SQL Server Express is nice but if they keep going this way MySQL is really going to kick 'em of the low end.
Great! But do we have any news regarding the "Generate scripts" problem mentioned above?
Thanks Aaron and OnlyOneN. This resolved connection problem between SQL Server Management Studio 2008 and Godaddy Hosted MSSQL DB.
Aaron Bertrand – you're a star for figuring this out! It's had me befuggled for days!
OnlyOneN – thanks for a bit more explanation. I'd hunted for the collate option, but it was your post that pointed me to right click the ACTUAL header of the column where it shows a list of options including COLLATE. Now everything works a treat!
Si
You saved my day.. thanks.
Thank you! I Managment Studio 2008 working with GoDaddy!. GoDaddy was blaming it on a firewall which made no sense at all.
Ditto to Pete's issue — I fixed the Collation problem when viewing databases, but I still get the error when I try to generate scripts using Tasks..Generate Scripts. Any ideas?
This solution fixed the initial problem for me however I get the same error when trying to script database objects. Any ideas how to fix that one.
For other newbie's such as myself, you have to click View, Object Explorer Details. Then, open Databases. Finally, click the header columns and uncheck Collation. I searched forever to find where to uncheck Collation. Only by sheer luck did I find it.
Thanks for helping me get this far, Aaron!
Hiya Aaron,
Unfortunately there are a number of items like this that can lock you out of a database with Management Studio (and sometimes completely!). Collation is one, the db_owner being NULL causes headaches – basically any of the base database properties. I taught a course once where one student thought it would be funny to set everyone's owner to NULL for the database in being used for the course. Nice.
You'd think MS would have safeguarded against these types of things, but if they did, what would the miscreants of the world do with their time 🙂