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 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:
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. (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:
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:
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';
If you receive a result set that resembles the following, the guest user has the necessary permissions.
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:
GRANT connect TO guest;
ID d'article : 2539091 - Dernière mise à jour : 21 juin 2014 - Révision : 1