Home Dashboard Directory Help

SSMS 2008 database list tries to default to a database on SQL Server 2005 by Danny.Su


Status: 

Active


11
0
Sign in
to vote
Type: Bug
ID: 354322
Opened: 6/30/2008 10:11:34 AM
Access Restriction: Public
3
Workaround(s)
view
6
User(s) can reproduce this bug

Description

When there is sql server 2005 instance with multiple databases SSMS 2008 seems to default to a database right away and when you don't have access to that database it throws an error and refuses to list the databases. I tried on with SMSS 2005 and I did not get this error for the same server. Everything is fine when connecting to a Sql server 2008 server. In the connection properties I already tried setting the database to the one I have access to and it still gives me this error.

Here is the error I get by just trying to list the databases on the server to navigate to mine.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server principal "my_user_name" is not able to access the database "SQL2005_Random_Database_on_the_server" under the current security context. (Microsoft SQL Server, Error: 916)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3239&EvtSrc=MSSQLServer&EvtID=916&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
Details
Sign in to post a comment.
Posted by pradyumansinh jadeja on 1/10/2013 at 8:38 PM
Same problem has been face at my end, and solution provided by Buck Woody on dated 7/1/2008 is working to get list of databases on SQL Server Management Studio 2008.
Posted by Bagsha on 8/25/2008 at 6:26 PM
...but I did get it to work by removing the Size(MB) column. Having the Collation column in there still works tho.
Posted by Bagsha on 8/25/2008 at 6:22 PM
I also want to add that the stated workaround, removing the collation column from OED, does not work when connecting to a SQL 2000 instance from SSMS 2008.
Posted by Bagsha on 8/25/2008 at 6:15 PM
This also occurs when trying to access a SQL 2000 instance from SSMS 2008. Because I don't have any rights to the very first database on the instance, SSMS 2008 errors out with the error stated in this issue, and then does not continue to show the databases I do have access to on that instance.

This is not good.
Posted by Microsoft on 7/8/2008 at 9:09 AM
Good catch - this has been recorded and corrected.

Thanks for submitting!

- Buck
Posted by AaronBertrand on 7/6/2008 at 2:33 PM
I have one instance of SSMS 2008 that is currently completely frozen because of this issue (no error message, just a unresponsive SSMS). I hope that I will be able to recover the files I was working on.

Buck, the issue *is* dependent on SSMS 2008. As the OP explained, I also could not reproduce the issue using SSMS 2005. And in my case, this auto-close setting is dictated by the hosting provider, not me. While I can certainly go and try to apply the workaround, I think if this comes out of the box this way, there are going to be a lot of people calling and yelling at their service provider and not necessarily finding this issue and workaround...
Posted by Danny.Su on 7/1/2008 at 4:20 PM
Thanks that workaround seemed to work. One quick thing I don't know if it is a bug or not and i don't want to open another ticket but when I right click on the on the databases I have access to and go to properties I get this error. I tried it on SSMS 2005 and I didn't get the error. connecting on the same server.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

------------------------------

Unable to cast object of type 'System.DBNull' to type 'System.String'. (Microsoft.SqlServer.SqlEnum)

------------------------------
BUTTONS:

OK
------------------------------
Posted by Microsoft on 7/1/2008 at 1:37 PM
Hello Danny - This issue would not occur on an instance with no auto-close databases. I could repro it only when I have a database with auto-close on. Also, this is independent of Yukon/Katmai.

By default, 'Collation' is on in the Object Explorer Details columns. This triggers a query to fetch the Collation status, which is NULL for non-online databases (which include offline and auto-close). That can cause an issue.

The workaround is:
1. Navigate to 'Databases' in OE and open OED in that context
2. Right click on the column header and unselect 'Collation'
3. Refresh the server in OE and enumerate the databases

Or, you can turn off the "Auto Close" setting for that database, unless you need it specifically.

We have another bug on this, set for the next major release.

Thanks!

Buck Woody, SQL Server Program Manager
Posted by Microsoft on 7/1/2008 at 9:36 AM
Hello Dan -

We're looking into this now. Thanks!

Buck Woody, SQL Server Program Manager
Sign in to post a workaround.
Posted by Sorin Dolha on 9/22/2008 at 2:13 AM
Log on to the SQL Server 2005 with an administrative account and run a query on each database that has SHUTDOWN state applied (check the Database State value in Properties/Options). This will remove the SHUTDOWN state from the database (database state will be set to NORMAL instead of NORMAL | SHUTDOWN, for example).

Note: This resolution is temporary. Eventually the database state may have SHUTDOWN applied again, and the issue would reappear.
Posted by Sorin Dolha on 9/22/2008 at 2:12 AM
Log on to the SQL Server 2005 with an administrative account, and add a
User Mapping for Domain Users group to each unassigned databases that have the SHUTDOWN state applied (check the Database State value in Properties/Options). Keep only Public permission for the user mapping, so that you won't provide any new real permissions for the domain users on the database.
Posted by Bagsha on 8/25/2008 at 6:27 PM
When connecting to a SQL 2000 instance from SSMS 2008, remove the 'Size (MB)' column from Object Explorer Details.