Home » CodeProject » The server principal “xxxx” is not able to access the database “msdb” under the current security context.

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

Problem :

Yesterday Night, One of my team member called & report that some users are getting below error no. 1 while connection to 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 : –

Most suspicious thing was, some users are facing issue & some user are working fine. When I goes into depth, I have found that user have super rights on SQL Server & on MSDB are working fine.

That means, it is clearly a permission issue but it is effecting 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 user sepratly

2) Provide CONNECT permission to PUBLIC role

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

GRANT CONNECT TO PUBLIC

If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun

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


7 Comments

  1. alzdba says:

    I would have double checked the guest account in msdb in stead of just granting public.
    ref: http://support.microsoft.com/kb/2539091

  2. microdermabrasion cost says:

    Good article. I certainly appreciate this site. Keep it up!

  3. Gerald says:

    Thanks for sharing this fantastic internet site.

  4. Jorge Rodríguez Leiva says:

    This should help:

    USE msdb;
    GRANT connect TO guest;
    GO

  5. Jason Clark says:

    Thank you very much. i am struggling with this problem from last 2 days and found another helpful article see here: http://www.sqlserverlogexplorer.com/fix-error-code-916/

Leave a Reply to Jorge Rodríguez Leiva Cancel reply

Join us on Facebook

microsoftcommunitycontributor
%d bloggers like this: