Click here to Skip to main content
13,402,670 members (39,734 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

27.7K views
Posted 19 Feb 2013

The Server Principal “xxxx” is Not Able to Access the Database “msdb” under the Current Security Context.

, 19 Feb 2013
Rate this:
Please Sign up or sign in to vote.
The server principal “xxxx” is not able to access the database “msdb” under the current security context.

Problem

Yesterday night, one of my team members called & report that some users are getting below error no. 1 while connecting to the SQL server & error no. 2 while trying to open Management folder.

Error 1: Cannot display policy health state at the server level, because the user doesn’t have permission. Permission to access the msdb database is required for this feature to work correctly.

Cannot display policy health state at the server level, because the user doesn’t have permission

Cannot display policy health state at the server level, because the user doesn’t have permission.

Error 2: The server principal “xxxx” is not able to access the database “msdb” under the current security context. (Microsoft SQL Server, Error: 916).

The server principal is not able to access the database msdb under the current security context

The server principal is not able to access the database msdb under the current security context.

Analysis & Resolution

The most suspicious thing was, some users are facing issues & some users are working fine. When I go into depth, I found that users that have super rights on SQL Server & on MSDB are working fine.

That means, it is clearly a permission issue but it is affecting random users in bulk.

Reason in my case: CONNECT permissions are denied from PUBLIC role.

Query to Check CONNECT Permissions

USE MSDB
GO
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’

We have 2 possible solutions:

  1. Provide connect permissions to all users separately
  2. Provide CONNECT permission to PUBLIC role

We have resolved the issue by running the below command. Connect permissions were reestablished on PUBLIC role.

GRANT CONNECT TO PUBLIC

Reference : Rohit Garg (http://mssqlfun.com/)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180218.2 | Last Updated 19 Feb 2013
Article Copyright 2013 by Rohitmssqlfun
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid