Since eUKhost has started providing Windows 2008 Server with Shared hosting as well as with Windows dedicated Server hosting there has been allot of new errors coming across. Also ASP .NET with Visual Studio 2008 and MS SQL 2008 has been widely used after the final release of Windows 2008 Server along with MS SQL 2008. The most command error that we have seen in combination of MS SQL 2005 and SQL 2008 is:
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
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server principal "my_db_user" is not able to access the database "any_other_db_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.1399&EvtSrc=MSSQLServer&EvtID=916&LinkId=20476
This is something similar to the image below:
This error happen to appear while remotely connecting a database on Microsoft SQL server 2005 with any version of Microsoft SQL Server Management Studio 2008, It will not appear if you connect the SQL server 2005 with Microsoft SQL Server Management Studio Express 2005. And the link that has been specified in the error above is of any no use, also the error started to be reported more for a Windows Shared hosting clients rather than the one with Windows dedicated server. Because the problem only appears to happen if you connect with a user that has access to a particular database and not with the user that has administrator access over the Microsoft SQL server. I had to scratch my head for the solution as it seems to be related more to the permissions on the database user. Now the interesting thing here is that the SQL database that in the error is different than the MSSQL database on which the user has its access on.
After allot of searching on the web I found that this is a bug on all versions of Microsoft SQL Server Management Studio (Express) 2008 and there is a simple work around to access your database remotely with no errors at all. Here is what that is required:
1) Connect to the MS SQL server with the user credentials in MSSMS (Express) 2008.
2) Bring Object Explorer Details window by selecting View –> Object Explorer Details in menu (or just by hitting F7)
3) In Object Explorer window click at Databases folder
4) In Object Explorer Details Window right-click at the column header and deselect Collation
5) Refresh Databases folder.
Refer to the image below for better understanding:
That is it. This will give you the desired access over your database and you will be happily make changes as per your need.
This is just another complicated error by Microsoft which has a very simple fix.