There is a bug within SSMS 2008 where if you connect to a db server using an account that does not have any rights to some of the database you can get an error when trying to expand the database list preventing you from accessing those databases that you do have rights to.
The error you receive looks something like this:
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 "CMSi-NATBN-Admin" is not able to access the database "CAMS-CCW-WCP2010" under the current security context. (Microsoft SQL Server, Error: 916)
The error is caused because SSMS fails when trying to read some of the information about the databases, in oder to fix the problem you have to configure that specific SSMS installation to not read that data about that database server. You do this as follows:
- Run SSMS and connect to required db server as sa or admin user account.
- Press F7 to open the "Object Explorer Details" window
- In the object explorer details window expand double click on "Databases"
- Right click on the header bar ("Name", "Recovery Model", etc) and deselect "Collation"
- Disconnect from the db server
- Reconnect to db server using desired account and test