I got this error recently - seemingly for no reason. I thought it might be an idea to share the workaround, as I didn’t find it anywhere when searching (although I did find a mass of other potential solutions - so this is the post I wanted to find while searching).
The Problem
I started getting the error:
The server principal “server” is not able to access the database “DBName” under the current security context.
This occurred after recreating a database and trying to run a stored procedure. It’s obviously a security error; but it appeared that the security was sufficient to execute the SP in question.
Attempt 1
The first thing I came across was this:
[sourcecode language=“SQL”] SELECT USER_NAME(P.GRANTEE_PRINCIPAL_ID) AS PRINCIPAL_NAME, DP.PRINCIPAL_ID, DP.TYPE_DESC AS PRINCIPAL_TYPE_DESC, P.CLASS_DESC, OBJECT_NAME(P.MAJOR_ID) AS OBJECT_NAME, P.PERMISSION_NAME, P.STATE_DESC AS PERMISSION_STATE_DESC FROM SYS.DATABASE_PERMISSIONS P INNER JOIN SYS.DATABASE_PRINCIPALS DP ON P.GRANTEE_PRINCIPAL_ID = DP.PRINCIPAL_ID WHERE P.STATE_DESC = ‘DENY’
That showed nothing; i.e. there was no explicit "deny".
**Attempt 2**
Next, I came across something that suggested using the following to explicitly grant connect to the current user:
[sourcecode language="SQL"]
use msdb
grant connect to [domaincurrentuser]
Again, this didn’t work for me.
Attempt 3
I then came across a raft of suggestions to do the following:
- Log in with Microsoft SQL Server Management Studio and click OK when the error appears.
- Press F7 to open Object Explorer Details.
- Right click on the column header at the top of the new window and uncheck Collation
- In the left column, right-click Databases and click Refresh
- Find your database and click the + sign to expand it.
It didn’t work (and I couldn’t really work out why it would); but based on the quantity of suggestions and positive response - try it).
Attempt 4
Try running the SP as “sa”. If it still doesn’t work then:
[sourcecode language=“SQL”] ALTER DATABASE DBName SET TRUSTWORTHY ON
sp_changedbowner ‘sa’
If you're still reading then you may be in the same boat as me; try this:
**Attempt 5 - success**
[sourcecode language="SQL"]
USE DBName
GO
EXECUTE sp\_grantdbaccess guest
/\*
REVOKE CONNECT FROM guest
GO
\*/
This WORKED, and I didn’t know why. Firstly, if this IS your problem; don’t leave guest enabled - it’s a LARGE security hole. What it likely means is that your SP is executing as a user that may not exist in your DB. Have a look for a command such as the following somewhere in the SQL:
[sourcecode language=“SQL”] EXECUTE AS ‘SomeUser’
Where 'SomeUser' isn't in your DB. The reason that enabling 'Guest' works is that it acts as a fallback; if you try to do something as a user with no permission, it will use Guest if it can't execute.