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](https://mssqlfun.files.wordpress.com/2013/01/cannot-display-policy-health-state-at-the-server-level-because-the-user-doesn_t-have-permission.png?w=660)
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](https://mssqlfun.files.wordpress.com/2013/01/the-server-principal-is-not-able-to-access-the-database-msdb-under-the-current-security-context.png?w=660)
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’
![](https://mssqlfun.files.wordpress.com/2013/01/image004.png?w=660)
We have 2 possible solutions:
- Provide
connect
permissions to all users separately - 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
![](https://mssqlfun.files.wordpress.com/2013/01/image003.png?w=660)
Reference : Rohit Garg (http://mssqlfun.com/)
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.