SQL Database Repair

Home » Blog

Each user owns a default database on SQL server. When SQL Server faces connection troubles, then it may restrict your access to the database. Mainly it is because of connecting to a running server without specifying a login database. In such a case, the user may either encounter error message 4064 or error message 4062, and following text message is displayed:

“Cannot open user default database”

Possible Reasons for the occurrence of the Error 4064

SQL Server throws an error message when go through any kind of unfavorable circumstances, like the one discussed above. The reason behind this error message is the connection unavailability or failure of providing the login database, due to which the default database is used for connection.

There could be various reasons that user default database is not available at the moment of connection

  • Database is offline
  • Enabled to emergency status
  • The database is no longer existed
  • Database has been detached
  • Someone or something else is using the available connection (in case of single user mode)
  • Database is in suspect mode
  • Found to in RESTRICTED_USER state
  • Fragment of a database mirror
  • User does not have the access permission
  • Database does not hold a login account mapped to a user

However, there could be other reasons as well, resulting into the database unavailability at the moment of connection, such as the login account is being shared with multiple groups and in which, one of groups for the default database is unavailable.

Resolution for Error Message “Cannot open user default database”

Method I: Manual Resolution

To resolve the SQL server Error message 4064, you need to provide a valid yet available default database for the connection. Make sure you own a user login privilege for the respective database wherein you can modify logins. So that you can switch the user’s default to the one, which is currently present for a connection.

Fix the error message “Cannot open user default database”, by implementing the following steps precisely:

For SQL Server version 2005 and above

Make use of sqlcmd utility, to modify the user default database in SQL server 2005, 2008 and above versions. Here are the steps to do the procedure:

  1. Go to ‘Start’ button and click Run. Now type cmd on it, and press ENTER.
  2. Apply one of the given methods as per the type of authentication used by SQL Server login:
      • If Microsoft Windows authentication is being used by SQL server login, then enter the following command at the command prompt, and simply hit the ENTER key:

    sqlcmd –E -S InstanceName –d master

      • If Microsoft Windows authentication is being used by SQL server login, then enter the following command at the command prompt, and simply hit the ENTER key:

    sqlcmd -S InstanceName -d master -U SQLLogin -P Password

    Note: In the above mentioned commands, the used terms indicate:

      • InstanceName is used as a placeholder for SQL Server 2005 instance’s name on which you are building the connection.
      • Also, the SQLLogin is even a placeholder for the SQL server login for default database that has been changed.
      • Password is even used as a placeholder for password of the SQL Server login.
  3. Again on the sqlcmd prompt, type the below given command precisely and hit ENTER key:

    ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName

    Note : In the above mentioned command, the used term indicate:

      • AvailDBName is a placeholder that is used as a name of an existing database which can be accessed using SQL Server login in the instance.
  4. Now at the sqlcmd utility prompt, write GO, and press the ENTER key.

For SQL Server version 7.0 and SQL Server version 2000

For these SQL server versions, you can use osql tool to modify the default database for the SQL Server 7.0 and SQL Server 2000. To proceed further, implement the following procedure:

  1. At the osql prompt, write the below given command and press ENTER key:

    AC:\>osql -E -d master

  2. On the command prompt, type the given command and hit the ENTER button:

    1>sp_defaultdb ‘user’s_login’, ‘master’

  3. Now at the second prompt, write the below provided command and again press the ENTER button:

    2>go

Method II: Third-party SQL Server Recovery Software

Unfortunately, if this SQL server error is not fixed using the manual methods, then try using a professional SQL server recovery tool. SQL server recovery tool is a reliable third-party software that offers complete solution to recover corrupt and inaccessible database files (.mdf) in order to smoothly resume the working process.

The software meticulously recovers the database objects like tables, views, triggers, stored procedures, indexes, pre-defined defaults, unique keys, foreign keys, primary keys, rules, checks, default values, and user data types from the corrupt MDF files with total data integrity. Interestingly, software is capable to repair large-size MDF files at once and then restore the recovered files directly to the running SQL server in just a single attempt. The best part is its interactive graphical user interface design to carry-out effective SQL database recovery operation. For more information visit http://www.en.sqldatabaserepair.org