I have a shared hosted SQL Server 2005 at an ISP, that I can connect to from SQL Server 2005 Management Studio. However, I cannot connect from SQL Server 2008 Management Studio.
I log on with SQL Server authentication (username+password).
When I connect, I get the error:
The server principal "MyUserName" is not able to access the database [name of first database on server – 00_something – not mine] under the current security context.
How can I get around this, and just get straight to the one database I do have access to on this server? I tried setting the "Default database" for the connection to the name of my database, but it does not change anything.
I would really like to not have to install SQL Server 2005 Management studio to connect to this one database…
Best Answer
This sounds like the same issue you're running into...
The problem is that for a database that is offline or has been auto-closed, collation comes back as NULL, and SSMS assumes it's because you don't have permission.
The fix is to go to Object Explorer Details, right-click the column header list, uncheck Collation, and refresh the Databases node. You should see all of the databases on the server at this point.
More detail can be found in Microsoft Connect #354291 and Microsoft Connect #354322