Select the product you need help with

You should not disable the guest user in the msdb database in SQL Server

Article ID: 2539091 - View products that this article applies to.

SUMMARY

SQL Server Books Online recommends that you disable the guest user in every database as a best practice for securing the database server. This recommendation does not apply to master, msdb, and tempb system databases. 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.

For more information about this recommendation in SQL Server Books Online, visit the following Microsoft Developer Network (MSDN) website:

Information about limiting access to data in SQL Server

(http://msdn.microsoft.com/en-us/library/ff848752.aspx)

MORE INFORMATION

Symptoms when the guest user is disabled in the msdb database

When the guest user is disabled in the msdb database, you may receive error 916

(http://msdn.microsoft.com/en-us/library/ee342155.aspx)

when either the Databases node in Management Studio expands or when a server application such as OCS 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. The first two 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 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.

Symptom 2

In SQL Server 2005, when a user who is not a member of the Sysadmin fixed server role in SQL Server, and who is not otherwise granted appropriate permissions in msdb, can expand the Databases node. However, when the user tries to view the properties of a database, they receive the following error message:

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 Principal name is not able to access the database "msdb" under the current security context. (Microsoft SQL Server, Error: 916)

Note The error message occurs even when the user is a member of the dbo role in that database.

Symptom 3

One of the products affected by this issue is Microsoft Office Communications Server 2007 (OCS 2007). The front-end services do not start when you try to start the server. Additionally, you see the following error message in Event Viewer:

Event Type: Error
Event Source: OCS User Services
Event Category: (1006)
Event ID: 30962
Date: Date
Time: Time
User: N/A
Computer: Computer_name
Description: Connection to back-end database succeeded, but failed to execute registration stored procedure on the back-end. This error should not occur under normal operating conditions. Contact product support.
Back-end Server: Server_name Database: rtc Sql native error: 916 Connection string of: driver={SQL Native Client};Trusted_Connection=yes;AutoTranslate=no;server=Server_name;database=rtc;
Cause: Possible issues with back-end database.
Resolution:
Ensure the back-end is functioning correctly.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

(http://go.microsoft.com/fwlink/events.asp)

For more information, see the following thread in the OCS support forum:

http://social.microsoft.com/Forums/en-US/communicationsserversetup/thread/8b428bd3-0509-4859-ac11-122bdbef06cc

(http://social.microsoft.com/Forums/en-US/communicationsserversetup/thread/8b428bd3-0509-4859-ac11-122bdbef06cc)

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.

Collapse this tableExpand this table

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.