Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


14
0
Sign in
to vote
Type: Bug
ID: 354291
Opened: 6/30/2008 7:22:23 AM
Access Restriction: Public
2
Workaround(s)
view
3
User(s) can reproduce this bug

Description

I am running into an issue that I really need resolved today. Simply put, I am getting the error:

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

But, the details are a little fuzzy

I have two installations of SQL Server 2008 RC0.

Microsoft SQL Server 2008 (CTP) Enterprise Edition (64-bit) CTP is installed on one server running Windows Server 2008 x64.
And, Microsoft SQL Server 2008 (CTP) Enterprise Edition CTP is installed on the other server running Windows Server 2003.

Both were installed with the same installation options (the SQL services are all running using a local admin account).

I created two databases on the x64 server:

db: dhtest
user: dhtest with dbo permission dhtest

db: dhtest2
user: dhtest2 with dbo permissions to dhtest2

I then try connecting via sql server management studio using SQL authentication and get this error:

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

But . . . I can connect to the x64 server from the server running Windows Server 2003 with SQL server management studio just fine. No errors at all and I have access to the assigned database as expected.

I also tested connecting from the x64 server to the Windows server 2003 with sql management studio and got the same error.

So... it appears to be a security setting ont the x64 box.

In event viewer I see the errors below but it doesn't make sense because the Windows Firewall is turned off.

The Windows Filtering Platform has blocked a bind to a local port. Application Name: \device\harddiskvolume1\windows\system32\svchost.exe
Network Information:
Source Address: 0.0.0.0
Source Port: 59535
Protocol: 17

Process ID: 240
Application Name: \device\harddiskvolume2\program files (x86)\microsoft sql server\100\tools\binn\vsshell\common7\ide\ssms.exe

If anyone has any suggestions at all, I would greatly appreciate it.
Details
Sign in to post a comment.
Posted by Moinur on 8/7/2012 at 9:23 AM
Dear Sreekar:

Thanks a lot. After install the SQL Server 2008 SP1 my problem solve. Big relief. Thanks again.

-
Jishan Ahmed
http://www.ApnerDeal.com
Posted by Pargat Singh Randhawa on 4/7/2011 at 11:22 PM
Thanks Ed McCarroll,
It works fine....
Posted by Microsoft on 11/18/2010 at 12:26 AM
This issue is fixed and already released in SQL Server 2008 SP1. Please install it for the fix.

Thanks
Sreekar
Posted by Microsoft on 10/19/2010 at 1:24 AM
This issue is fixed and already released in SQL Server 2008 SP1. Please install it for the fix.

Thanks
Sreekar
Posted by Microsoft on 7/12/2010 at 11:11 PM
This issue is fixed and already released in SQL Server 2008 SP1. Please install it for the fix.

Thanks
Sreekar
Posted by Edward McCarroll on 8/8/2008 at 6:45 PM
This suggestion solved my problem:

Please try using the following workaround and let us know if that helps:
1) Bring Object Explorer Details window by selecting View --> Object Explorer Details in menu (or hitting F7)
2) In Object Explorer window click at Databases folder
3) In Object Explorer Details window right-click at the column header and deselect Collation
4) Refresh Databases folder.

Thanks Stanislav
Posted by Microsoft on 7/7/2008 at 10:46 AM
This is a duplicate of another bug we have in this area. I will close your request out, but we will still be working this issue.

Thanks!

Stan - SQL Server Developer
Posted by dbh on 7/1/2008 at 2:41 PM
To clarify, the fix was to right-click at the column header and select 'Reset View'. Thanks again!
Posted by dbh on 7/1/2008 at 2:00 PM
OMG!! That fixed it. Thank you, Thank you! Do you mind explaining what caused it?
Posted by Microsoft on 7/1/2008 at 12:41 PM
Hello,

This seems to be similar to SSMS Object Explorer issue discussed here:
http://forums.microsoft.com/msdn/showpost.aspx?postid=3531315&isthread=false&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0

Please try using the following workaround and let us know if that helps:
1) Bring Object Explorer Details window by selecting View --> Object Explorer Details in menu (or hitting F7)
2) In Object Explorer window click at Databases folder
3) In Object Explorer Details window right-click at the column header and deselect Collation
4) Refresh Databases folder.

Thanks,
Stanislav Chiknavaryan, SQL Server Software Design Engineer.
Posted by dbh on 7/1/2008 at 10:43 AM
Regarding Comment #1

I have confirmed that all the SQL Services are running with a local administrator account and all the appropriate protocols are enabled. I tried connecting with the server name and IP address and both give the same error. I am able to connect with SA and expand the ‘Databases’ node. Also, I tried opening a case with Support but because it is specific to SQL Server 2008 RC0, I couldn’t open a case.

By the way, I want to make sure I am clear – I can connect to the server but I cannot expand the ‘Databases’ node within SSMS.

Regarding comment #2

Here is the situation:

We must use SQL Authentication within SQL 2008 because we’ll be hosting multiple databases for various clients. We expect that we can setup a database with an associated user account that has DBO access to the db. The client can then access the database remotely using SSMS, expand ‘Databases’, see a list of databases BUT only be able to access their database. I get the error as soon as I try to expand the ‘Databases’ node when logged in with dhtest. Also, the only items I see under the ‘Databases’ node is ‘System Databases’ and ‘Database Snapshots’. I do not see any user databases.

Yes, I have confirmed that I can connect to the dhtest user database via sqlcmd and execute a simple select statement.

I use SSMS to create the databases and associated user accounts. Here are the instructions I follow:

Login to the server (x64 server)
1.    Open SQL Server Management Studio and connect to the server
2.    Right-click on Databases and select New Database…
3.    Give the database a name and set the owner to SA and click OK.

Setup a user account
1.    Expand Security and right-click on Logins and then select New Login…
2.    Type in a user name, select SQL Authentication, type in a strong password, uncheck Enforce password policy and select the database you just created as the default database.
3.    Click on the User mapping option on the left side, find the database you just created and select it, set the schema as DBO, and then select dbowner for the Database role membership
4.    Click OK and test.

Please let me know if you have any suggestions.
Posted by Microsoft on 6/30/2008 at 3:24 PM
The error (The server principal "dhtest" is not able to access the database "dhtest2" under the current security context.) indicates that you are trying to access db dhtest2 as a dhtest principal, but your description of the problem indicate that dhtest2 db is owned by dhtest2 principal not dhtest one. If dhtest doesn't have any permission to access dhtest2 the error looks reasonable. Please, clarify what you are trying to do. Which database you are trying to access? At which momend do you get this error message? Can you connect form the same x64 machine w/o managment studio? Just running sqlcmd? Could you also include the exact create db and create login statements you used to create the db's and logins and to assign the ownership to the principals.

Thanks!
Posted by Microsoft on 6/30/2008 at 10:54 AM
Hello -

Let's follow the standard troubleshooting process here - ensure that all services are started (like the SQL Browser), that you're trying to connect with TCP/IP, that the sa can connect and so on. I can't seem to reproduce this here with the defaults, so it could be a specific setting on your side. If you still have an issue, I'd recommend opening a case with support. I'll forward this bug on to a member of the protocols team to follow up with you.

Thanks -

Buck Woody, SQL Server Program Manager
Sign in to post a workaround.
Posted by Kishor Shirsath on 10/25/2010 at 3:46 AM
Check whether the SP created on the specified server is using "WITH EXECUTE AS OWNER" clause.

We were facing same kind of issue which was resolved by removing the above said clause from the SP.
Posted by SQLDBA_Quest_4_Knowledge on 4/15/2010 at 8:06 AM
The header actually has options that show default information. If you don't have access
you will get this error.

To Fix hit "f7" key right click on the header and uncheck the options. This
should fix the issue. Un check all the options to resolve and then start addig the ones you want one by one.
File Name Submitted By Submitted On File Size  
SQL08-error.docx (restricted) 7/1/2008 -