SQL Server Error 4064 – Causes and Resolutions

Errors are unavoidable in many circumstances when using MS SQL Server irrespective of the version, be it 2000, 7.5, 2005, 2008, etc. One common error that is familiarized with almost all editions of SQL server is as follows:

Error 4064: "cannot open user default database login failed"

Each error of SQL server is identified by a number and the error message mentioned above occurs when user is tried to connect to an instance of SQL server. The message is identified as SQL error 4064.

Each user who has an account in the concerned server owns a database by default. When attempts are made to connect to a SQL Server without specifying a login database, the default database is utilized. But connection might fail if the default database is also unavailable at the time of the linkage. Instead SQL Server error 4062 or 4064 is received.

There exists a lot more reasons for the error, such as due to unavailability of user default database at the time of connection. It might be that the database which stores information is in suspect mode or offline. There also exists the possibility that it has been set to RESTRICTED_USER state or has been left detached. Other variety of cause may be as follows that database:

  • Exists no more.
  • Is set to urgent situation status.
  • Exists in single user mode and the single available connection is already being used by someone else or any other thing.
  • Does not have an account login mapped to a user.
  • User has been deprived access.
  • And is a part of database mirror.

In addition, the login account may be a part of multiple groups and the default database for one of the groups is occupied at the time of connection.

How To Fix SQL Error Code 4064

To get rid of the error "Cannot open user default database login failed" different methods need to be adopted depending on the server environment, to alter user's default database to a database that is available for a connection.

In SQL version 2000 and 7.0 - OSQL utility can be used to change the default database. For recovery using OSQL tool following steps are required.

  • In the command prompt window, type 'C :\> osql -E -d master' and press Enter key.
  • At the OSQL prompt, type '1>sp_defaultdb 'user's_login', 'master'' and press Enter.
  • Type '2>go' and then press Enter button, at second prompt.

In SQL Server 2005 and newer releases - SQLCMD utility can be used to change the default database of server. Following are the steps for the process:

Step1 - Go for Start, click Run, type "cmd" and press Enter.

Step2 - Use one of the following methods, depending on the nature of authentication that SQL login utilizes:

  • If Windows authentication is used to connect to the instance, type "sqlcmd –E -S InstanceName –d master" at the command prompt and then press Enter key.
  • If SQL Server authentication is used to connect to the instance, type the command "sqlcmd -S InstanceName -d master -U SQLLogin -P Password" and then Enter.

Step3 - At sqlcmd prompt, type in "ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName" and then press Enter.

Note - AvailDBName is a placeholder for the name of existing database that can be accessed in instance by SQL Server login.

Step4 - Then at 'sqlcmd prompt', GO should be typed in and then Enter should be pressed.

Important Note – It can be said that methods discussed above certainly prove to be successful to fix SQL error number 4064. But problem arises when the database file is protected with a password which is unknown. At times of unknown password an external tool becomes necessary to be employed. A third party software application named SQL password recovery tool is a good option to erase and reset SQL database file password. To use this tool SQL Server should be installed on the system but should be stopped while loading primary file to execute.

Download Now   Purchase Now

sql password recovery banner