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).
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.
The first thing I came across was this:
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”.
Next, I came across something that suggested using the following to explicitly grant connect to the current user:
use msdb grant connect to [domaincurrentuser]
Again, this didn’t work for me.
I then came across a raft of suggestions to do the following:
1. Log in with Microsoft SQL Server Management Studio and click OK when the error appears.
2. Press F7 to open Object Explorer Details.
3. Right click on the column header at the top of the new window and uncheck Collation
4. In the left column, right-click Databases and click Refresh
5. 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).
Try running the SP as “sa”. If it still doesn’t work then:
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
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:
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.