Error message when you connect to an instance of SQL Server: "Cannot open user default database"

Article translations Article translations
Article ID: 307864 - View products that this article applies to.
Expand all | Collapse all

On This Page

Symptoms

Each user has a default database. When you connect to computer that is running Microsoft SQL Server, and you do not specify a login database, the default database is used. However, if the default database is unavailable at the time of the connection, you may not be able to connect. Instead, you receive error message 4062 or error message 4064. The text of the error messages is as follows:
Cannot open user default database

Cause

The user default database is unavailable at the time of connection. It is possible that the database:
  • Is in suspect mode.
  • No longer exists.
  • Is in single user mode and the only available connection is already being used by someone else or by something else.
  • Has been detached.
  • Has been set to the RESTRICTED_USER state.
  • Is offline.
  • Is set to emergency status.
  • Does not have the login account mapped to a user or the user has been denied access.
  • Is part of a database mirror.
Additionally, the login account may be a member of multiple groups and the default database for one of those groups is unavailable at the time of connection.

Workaround

To work around this behavior, specify a valid, available database in the connection string. To avoid the error when the user's default database is unavailable, log on as a user who can modify logins. Then, change the user's default database to a database that is currently available for a connection.

SQL Server 2005 and later versions

You can use the sqlcmd utility to change the default database in SQL Server 2005. To do this, follow these steps:
  1. Click Start, click Run, type cmd, and then press ENTER.
  2. Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:
    • If the SQL Server login uses Microsoft Windows authentication to connect to the instance, type the following at the command prompt, and then press ENTER:
      sqlcmd –E -S InstanceName –d master
    • If the SQL Server login uses SQL Server authentication to connect to the instance, type the following at the command prompt, and then press ENTER:
      sqlcmd -S InstanceName -d master -U SQLLogin -P Password
    Note InstanceName is a placeholder for the name of the SQL Server 2005 instance to which you are connecting. SQLLogin is a placeholder for the SQL Server login whose default database has been dropped. Password is a placeholder for the SQL Server login password.
  3. At the sqlcmd prompt, type the following, and then press ENTER:
    ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName
    Note AvailDBName is a placeholder for the name of the existing database that can be accessed by the SQL Server login in the instance.
  4. At the sqlcmd prompt, type GO, and then press ENTER.

SQL Server 2000 and SQL Server 7.0

You can use the osql utility to change the default database in SQL Server 2000 and in SQL Server 7.0. To do this, follow these steps:
  1. At a command prompt, type the following and then press ENTER:
    C:\>osql -E -d master
  2. At the osql prompt, type the following and then press ENTER:
    1>sp_defaultdb 'user's_login', 'master'
  3. At the second prompt, type the following and then press ENTER:
    2>go

Status

This behavior is by design.

More information

To demonstrate this behavior, try to connect with OSQL and use the -d option with an existing database that is in a normal online status. The connection succeeds without any error message.

Conversely, you receive responses that are similar to the following from various SQL Server client utilities when the database is unavailable:
  • SQL Server Management Studio (SQL Server 2005)
    Cannot connect to ServerName
    ADDITIONAL INFORMATION:
    Cannot open user default database. Login failed. Login failed for user 'UserName'. (Microsoft SQL Server, Error: 4064)
  • Using Sqlcmd utility in SQL Server 2005
    Msg 4064, Level 11, State 1, Server ServerName, Line 1
    Cannot open user default database. Login failed.
    Msg 18456, Level 14, State 1, Server ServerName, Line 1 Login failed for user 'UserName'.
  • SQL Server 2000 Query Analyzer (QA)
    Unable to connect to server TESTSERVER:
    Server: Msg 4064, Level 16, State 1
    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.
  • SQL Server 2000 Server Enterprise Manager (SEM)
    A connection could not be established to TESTSERVER - Cannot open user default database 'dbid'. Using master database instead.

    Please verify SQL Server is running and check your SQL Server registration properties (by right-clicking on the TESTSERVER node) and try again.
  • A command prompt using SQL Server 2000 OSQL

    You receive the following error message when you use the C:\>osql -E command:
    Cannot open user default database. Login failed.
  • SQL Server 7.0 Query Analyzer (QA)
    Unable to connect to server \\TESTSERVER:
    Server: Msg 4062, Level 16, State 1
    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database '<ID>'. Using master database instead.
  • A command prompt using SQL Server 7.0 OSQL

    You receive the following error message when you use the C:\>osql -E command:
    Cannot open user default database 'dbid'.
    Using master database instead.
The following table provides more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product against which the rule is evaluated.
Collapse this tableExpand this table
Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated
System Center AdvisorSQL Server server-level principals configured with non-existent default databaseSystem Center Advisor checks if any logins present in the SQL Server instance are configured with a default database that does not exist anymore. Review the details that are provided in the “Information Collected” section of the advisor alert, and follow the steps discussed in this article to resolve the situation. SQL Server 2008

SQL Server 2008 R2

SQL Server 2012

Properties

Article ID: 307864 - Last Review: June 22, 2014 - Revision: 10.0
Applies to
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Enterprise Evaluation
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • SQL Server 2012 Enterprise Core
Keywords: 
kbtshoot kbsqlsetup kbprb KB307864

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com