I was getting the error “The server principal "userID" is not able to access the database "xxxxxx" under the current security context. (Microsoft SQL Server, Error: 916)”, while trying to connect to a remote SQL Server database using “SQL Server Management Studio 2008”. After Googling for over an hour and not finding any solution, I tried connecting to the database using “SQL Server Management Studio Express 2005” and voila! I was able to successfully able to connect to the database.
More info:
The SQL Server database was remotely hosted by Verio.
I was able to successfully connect to the remote database. The above error occurred only when I tried to browse the tables stored in my database. It seems like SMS (SQL Server Management Studio) was trying to connect to a database called xxxxxx which did not belong to me. I tried setting the default database to my database, but that didn’t seem to help either. Not sure if there is another option that might help fix this issue using SMS 2008 (I am not a SQL Server DBA).
The only way I was able to browse the tables in my database was by using SMS 2005.
Get SMS 2005 Express from: http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en
UPDATE: January 23, 2009: The correct way to fix this problem:
I finally figured out the correct way to get around this problem, so that I could connect and view my tables using SQL Server Management Studio 2008. Not sure if it is a bug in SMS 2008 or not, but here is what you need to do:
1. Connect to the SQL Server
2. Click on the “Databases” node. If you get an error message, dismiss it.
3. Press “F7”, to show the Object Details panel (right hand side panel).
4. In the Object Details panel, right click on the column header and deselect the “Collation” header option.
5. Now refresh the database node by click the refresh button (
6. You should be good to go!
aaaah! Thanks, this really helped as I couldn't get 2005 installed, then it broke the 2008 management studio so I had to repair the installation. Nightmare! Thanks for documenting this!
ReplyDeleteThanks. However, I cannot find the column header...don't know what that is referring to. I have tried to right click everywhere on the screen but don't get the option to uncheck collation. Any ideas?
ReplyDeleteThe column header is the top portion of the "object explorer details" view (its the part that has "Name", "Policy Health State", etc...). Just right click on any one of those headers and you should be able to turn off the "Collation" option.
ReplyDeleteThank you so much! This is a life saver. I installed and used SQL 2008 Express under one account, which is now inactive, because I changed the domain on my computer. Now, using the new account I could not access the databases and the same error message was showing.
ReplyDeleteWell, I can see the databases, but I can't open it. My problem appears to be more complex.
ReplyDeleteExcellent, worked for me!
ReplyDeleteIt worked for me as well.
ReplyDeleteNote that one can use the command line version of query analyzer without any problems.
1) In the commmand prompt, go to
C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn
2)type: osql -S "database name" -U "username" -P "password"
3) You will be connected
You saved me!
ReplyDeleteThanks
THANKS!!
ReplyDeleteAwesome!!! I was on the phone with my shared host for an hour. Their suggestion was to re-install SQL Express Mgt Studio. Your advice did the trick and fixed the issue. Thanks.
ReplyDelete