Article ID: 2539091 - View products that this article applies to.
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
Symptoms when the guest user is disabled in the msdb databaseWhen 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 1In 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)
An exception occurred while executing a Transact-SQL statement or batch.
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 2In 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.
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 3One 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:
For more information, see the following thread in the OCS support forum:
How to determine the issueTo 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:
If you receive a result set that resembles the following, the guest user has the necessary permissions.
Collapse this tableExpand this table
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 issueTo resolve the issue, run the following query in SQL Server Management Studio as a member of the sysadmin fixed server role:
Article ID: 2539091 - Last Review: July 22, 2011 - Revision: 5.1
Contact us for more help
Connect with Answer Desk for expert help.