Error message when you log on to an instance of Microsoft SQL Server 2005 from SQL Server Management Studio: "Timeout expired. This timeout period elapsed prior to completion of the operation or the server is not responding"

When you log on to an instance of Microsoft SQL Server 2005 from SQL Server Management Studio, you receive the following error message:If you log on to the instance of SQL Server 2005 by using the Sqlcmd utility (Sqlcmd.exe) at a command prompt, you receive the following error message:Note ProtocolName represents the protocol that the SQL Server database engine currently uses to connect to the instance of SQL Server 2005.

Additionally, CPU utilization is 100 percent during the logon process.

This problem occurs if the following conditions are true:

• The SQL Server login that you use to log on to the instance of SQL Server 2005 is not a member of the sysadmin fixed server role.
• Lots of SQL Server logins exist in the instance of SQL Server 2005.

This problem occurs because the permission cache entries for the Server object are initialized during the connection process. During this process, the SQL Server login is not a member of the sysadmin fixed server role.

You experience 100 percent CPU utilization occurs when the permission cache for the Server object is initialized.

Note Other problems may also cause the error message that is mentioned in the "Symptoms" section.

To work around this problem, use one of the following methods.

Method 1

Reduce the number of SQL Server logins that are in the instance of SQL Server 2005.

If many users must have access to the instance of SQL Server 2005 from the Web, create a user table in the instance to manage these users in the instance. You do not have to create SQL Server logins for each user in the instance.

You can also configure the SQL Server logins to use Windows authentication to log on to the instance. To do this, follow these steps:

1. Create a Windows group.
2. Create a SQL Server login that is mapped to the Windows group.
3. Add the users to the Windows group.

Method 2

Populate the permission cache entries before you log on to the instance. To do this, log on to the instance by using a SQL Server login that is not a member of the sysadmin fixed server role. When the permission cache entries are populated, you experience the problem that is described in the "Symptoms" section. However, if you log on the instance again, the logon process will be fast.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

When you use a member of the sysadmin fixed server role to log on to the instance, the permission cache entries for the Server object are not initialized. However, you must be careful when you grant users the sysadmin fixed server role for security reasons.

For example, you have two SQL Server logins. One SQL Server logins is LoginA. The other SQL Server login is LoginB. Neither LoginA nor LoginB is a member of the sysadmin fixed server role. When you use LoginA to log on to the instance, the permission cache entries for the Server object are initialized. If many SQL Server logins exist in the instance of SQL Server 2005, the initialization process takes a long time to complete, and CPU utilization is 100 percent. After the logon process for LoginA is completed, any later logon processes will be fast. For example, if you try to use LoginB to log on to the instance of SQL Server 2005, the logon process will be fast even though you are using LoginB to log on to the instance for the first time.

When you log on to an instance of SQL Server 2005, the following behavior may occur:

• The Server object is invalidated.
• The permission cache entries for the Server object must be initialized.

This behavior occurs after you perform one of the following operations:

• You restart the SQL Server service.
• You run one the following statements in the instance of SQL Server 2005:
  • CREATE LOGIN
  • DROP LOGIN
  • DBCC FREESYSTEM CACHE ('ALL')
  • DROP SCHEMA
• You run one of the following statements in the Master database in the instance:
  • CREATE USER
  • DROP USER
  • DROP CERTIFICATE
  • DROP ASYMETRIC KEY
  • DROP SYMETRIC KEY
  • DROP APPLICATION ROLE