大网数据广告活动
数据库与应用安全

Error when you disable the guest user

· 52CE 我爱测

This article describes various issues that can occur when you disable the guest user in the msdb database in SQL Server.

This article describes various issues that can occur when you disable the guest user in the msdb database in SQL Server.

Original product version:   SQL Server
Original KB number:   2539091

Symptoms when the guest user is disabled in the msdb database

In order for some Microsoft SQL Server features to work, the guest user must be enabled in the msdb database. This article describes some issues that you may experience if you disable the guest user in the msdb database. The article also provides information about how to resolve those issues.

When the guest user is disabled in the msdb database, you may receive error MSSQLSERVER_916 when user expands Databases node in Management Studio expands or when a server application tries to connect to SQL Server. You may experience one or more of the following symptoms in your environment when this issue occurs.

Note: The text of the error may slightly vary, depending on the scenario. However, the underlying cause is essentially the same. That cause is insufficient privileges in the msdb database. These symptoms occur when Object Explorer tries to show the Policy Based Management status of each database. Object Explorer uses the permissions of the current logon to query the msdb database for this information, which causes the error.

Symptom 1

In SQL Server 2012 and later environments, when a user who is not a member of the Sysadmin fixed server role in SQL Server and not otherwise granted appropriate permissions in msdb tries to expand the Databases node or any of the folders under that node, they receive an error message that resembles the following:

Cannot show requested dialog. ADDITIONAL INFORMATION: Cannot show requested dialog. (SqlMgmt) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The server principal <username> is not able to access the database msdb under the current security context. (Microsoft SQL Server, Error: 916)

Symptom 2

In SQL Server 2008 and SQL Server 2008 R2 environments, when a user who is not a member of the Sysadmin fixed server role in SQL Server and not otherwise granted appropriate permissions in msdb tries to expand the Databases node or any of the folders under that node, they receive an error message that resembles the following:

Failed to retrieve data for this request. (Microsoft.SqlServer.Manager.Sdk.Sfc)
Additional Information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The server principal <Servername> is not able to access the database “msdb” under the current security context. (Microsoft SQL Server, Error: 916)

Note: Expanding the Database node is just one of the activities that requires connect permission for the guest account to the msdb database. A similar error can occur with any activity that requires at least minimal access to the msdb database.

How to determine the issue

To determine whether the guest user is configured correctly in the msdb database, run the following query as a member of the sysadmin fixed server role:

USE msdb;

SELECT prins.name AS grantee_name, perms.*

FROM sys.database_permissions AS perms

JOIN sys.database_principals AS prins

ON perms.grantee_principal_id = prins.principal_id

WHERE prins.name = 'guest' AND perms.permission_name = 'CONNECT';

GO

If you receive a result set that resembles the following, the guest user has the necessary permissions.

grantee_name class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
guest 0 DATABASE 0 0 2 1 CO CONNECT G GRANT

If you receive either an empty result set or if the state_desc shows DENY in the result set that is mentioned here, the guest user is disabled in the msdb database. You may receive error 916 when you connect to a database.

How to resolve the issue

To resolve the issue, run the following query in SQL Server Management Studio as a member of the sysadmin fixed server role:

USE msdb;

GRANT connect TO guest;

GO

References

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 11/19/2020
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。