July 7, 2008 | SQL Server

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.  So it throws up this error:

Permissions 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:

http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=355238

36 comments on this post

    • James Luetkehoelter - July 8, 2008, 6:45 PM

      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 🙂

    • OnlyOneN - December 23, 2008, 12:59 PM

      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!

    • pete - February 16, 2009, 2:04 AM

      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.

    • Andy - March 23, 2009, 4:34 PM

      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?

    • Mike - April 18, 2009, 5:23 PM

      Thank you! I Managment Studio 2008 working with GoDaddy!. GoDaddy was blaming it on a firewall which made no sense at all.

    • Azeem - April 23, 2009, 11:47 AM

      You saved my day.. thanks.

    • SimonR - April 28, 2009, 2:01 PM

      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

    • Richie - May 14, 2009, 5:51 PM

      Thanks Aaron and OnlyOneN. This resolved connection problem between SQL Server Management Studio 2008 and Godaddy Hosted MSSQL DB.

    • Alexandros - May 25, 2009, 8:28 PM

      Great! But do we have any news regarding the "Generate scripts" problem mentioned above?

    • Sander P - May 29, 2009, 4:33 AM

      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.

    • Rafael - June 9, 2009, 4:37 AM

      Thank you fixed for fixing the nagging GoDaddy issue!

    • A.Ghazal - July 15, 2009, 8:30 PM

      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

    • Kam - July 17, 2009, 1:16 AM

      Thnaks for this. You saved me hours of investigation and head scratching!

    • Dave Finlay - August 2, 2009, 7:27 AM

      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

    • Russ S. - August 13, 2009, 9:45 PM

      Thanks! I was totally stuck!

    • Steve D - October 15, 2009, 4:26 PM

      Thanks!  It works!

    • Jerome Gaynor - January 20, 2010, 6:51 PM

      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!

    • Tom Niehoff - February 10, 2010, 4:10 AM

      That works for getting a dump of the databases on the server, but how can you get access to the in the Object Explorer.  🙂

    • AaronBertrand - February 10, 2010, 5:05 AM

      Tom, I'm not sure I follow, can you elaborate?

    • Sanjay Verma - February 22, 2010, 12:23 PM

      It is the issue of providing rights. Give proper rights to the user with which you are logging in.
      Hope this helps.
      Sanjay Vema

    • AT - April 18, 2010, 7:01 AM

      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…

    • golshan - April 24, 2010, 6:07 PM

      this is the most helpfull page that i've ever visit on the web.
      thank u.

    • darkkevin - April 27, 2010, 7:35 PM

      Thank you so much! I really couldnt understand why this was happening!
      You're a god-send!
      Kev.

    • eric - May 13, 2010, 6:40 PM

      thanks a lot mate, you're a genius for figuring that out!

    • SohelElite - May 19, 2010, 5:30 PM

      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.

    • Saulo - June 1, 2010, 2:49 AM

      Thanks! And people ask why I curse MS…

    • Donald Klopper (South Africa) - June 8, 2010, 11:15 AM

      Thank you thank you thank you thank you thank you.

    • Ahmed - June 11, 2010, 1:21 AM

      Very Gooooooooooooooooood thanks alooooooooooooooooooooooooooooot

    • Euan - July 1, 2010, 12:45 PM

      This post was a lifesaver! Thanks.

    • Gautam Jain - July 1, 2010, 6:24 PM

      Thanks a lot. Your article saved a lot of time today.

    • Rachel - September 3, 2010, 9:36 PM

      Yes! Thank you OnlyOneN – I couldn't for the life of me find the collate option!

    • joscion - February 9, 2011, 2:59 AM

      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:)

    • Garrett - February 21, 2011, 10:09 PM

      Thank You!  Thank You!  Thank You!  

    • Parry - March 29, 2011, 2:49 PM

      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…

    • Rabin - May 2, 2011, 8:08 AM

      Thank you so very much. I ran into this particular problem, and you saved me. Thanx a tonne.

    • Laurie T - July 20, 2011, 6:13 PM

      Thank you so much for posting this solution.  It saved me countless hours of troubleshooting on my own.

Comments are closed.