THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a senior consultant for SQL Sentry, Inc., makers of performance monitoring and event management software for SQL Server, Analysis Services, and Windows. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at user group meetings and SQL Saturday events.

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://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=355238

Published Monday, July 07, 2008 1:38 PM by AaronBertrand
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James Luetkehoelter said:

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

July 8, 2008 11:45 AM
 

OnlyOneN said:

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!

December 23, 2008 4:59 AM
 

Aaron Bertrand said:

Cumulative Update Package 3 has been made available in KB #960484 . This will bring your SQL Server 2008

January 20, 2009 5:53 PM
 

pete said:

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.

February 15, 2009 6:04 PM
 

Andy said:

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?

March 23, 2009 9:34 AM
 

Mike said:

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

April 18, 2009 10:23 AM
 

Azeem said:

You saved my day.. thanks.

April 23, 2009 4:47 AM
 

SimonR said:

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

April 28, 2009 7:01 AM
 

Richie said:

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

May 14, 2009 10:51 AM
 

Alexandros said:

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

May 25, 2009 1:28 PM
 

Sander P said:

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.

May 28, 2009 9:33 PM
 

Rafael said:

Thank you fixed for fixing the nagging GoDaddy issue!

June 8, 2009 9:37 PM
 

A.Ghazal said:

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

July 15, 2009 1:30 PM
 

Kam said:

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

July 16, 2009 6:16 PM
 

Aaron Bertrand said:

In a shared SQL Server hosting environment, there are several problems that can arise when you let your

July 19, 2009 11:45 AM
 

Dave Finlay said:

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

August 2, 2009 12:27 AM
 

Russ S. said:

Thanks! I was totally stuck!

August 13, 2009 2:45 PM
 

Steve D said:

Thanks!  It works!

October 15, 2009 9:26 AM
 

Jerome Gaynor said:

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!

January 20, 2010 10:51 AM
 

Tom Niehoff said:

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

February 9, 2010 8:10 PM
 

AaronBertrand said:

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

February 9, 2010 9:05 PM
 

Sanjay Verma said:

It is the issue of providing rights. Give proper rights to the user with which you are logging in.

Hope this helps.

Sanjay Vema

February 22, 2010 4:23 AM
 

AT said:

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...

April 18, 2010 12:01 AM
 

golshan said:

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

thank u.

April 24, 2010 11:07 AM
 

darkkevin said:

Thank you so much! I really couldnt understand why this was happening!

You're a god-send!

Kev.

April 27, 2010 12:35 PM
 

eric said:

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

May 13, 2010 11:40 AM
 

SohelElite said:

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.

May 19, 2010 10:30 AM
 

Saulo said:

Thanks! And people ask why I curse MS...

May 31, 2010 7:49 PM
 

Donald Klopper (South Africa) said:

Thank you thank you thank you thank you thank you.

June 8, 2010 4:15 AM
 

Ahmed said:

Very Gooooooooooooooooood thanks alooooooooooooooooooooooooooooot

June 10, 2010 6:21 PM
 

Euan said:

This post was a lifesaver! Thanks.

July 1, 2010 5:45 AM
 

Gautam Jain said:

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

July 1, 2010 11:24 AM
 

Rachel said:

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

September 3, 2010 2:36 PM
 

joscion said:

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

February 8, 2011 6:59 PM
 

Garrett said:

Thank You!  Thank You!  Thank You!  

February 21, 2011 2:09 PM
 

Parry said:

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...

March 29, 2011 7:49 AM
 

Rabin said:

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

May 2, 2011 1:08 AM
 

Laurie T said:

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

July 20, 2011 11:13 AM

Leave a Comment

(required) 
(required) 
Submit

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement