[Fix] - SQL Server Error 4064 – Cannot open user default database. Login failed. Login failed for user

Wikitechy | 2507 Views | sql server | 05 Jun 2016

 

Scenario :    

Many users have received following error while connecting to the database. This error happens when database is dropped with which few database users are mapped. When that user tries to login the following error shows up.


FIX 1 :

    1.    Click on Options button in SQL Server’s “Connect to Server” dialog box.


    2.    Select “Connect to database” as “master” database.

    3.    Now go back to login tab and connect with any registered user via SQL Server authentication.

FIX 2 :

    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:
For Windows authentication : sqlcmd –E -S [InstanceName] –d master
For SQL authentication : sqlcmd -S [InstanceName] -d master -U [Login_Username] -P [Login_Password]
    3.    After executing one of the above commands, use the below command:
ALTER LOGIN [Login_Username] WITH DEFAULT_DATABASE = [DB_Name]
    4.    Finally type GO and press ENTER to save the changes made to the user.

Fix 3 :

To avoid this error, while creating logins, check for the default database option. So now login as administrator or with high credentials, click the appropriate user login and set the default database as master and click ok button.


Fixes are applicable to the following versions of SQL Server:

  • SQL Server 1.1 (16 bit)
  • SQL Server 4.2A (16 bit)
  • SQL Server 4.2B (16 bit)
  • SQL Server 4.21a
  • SQL Server 6.0
  • SQL Server 6.5
  • SQL Server 7.0
  • SQL Server 7.0 OLAP Tools
  • SQL Server 2000
  • SQL Server 2000 64-bit Edition
  • SQL Server 2005
  • SQL Server 2008
  • Azure SQL DB
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016

Related Error Tags :

  • Error message when you connect to an instance of SQL Server
  • Error : "'Cannot Open User Default Database" occurs when logging into SQL server
  • Error in login to SQL Server
  • Cannot open user default database on SQL Server
  • SQL Error 4064 – Its Causes and Resolutions


Workshop

Bug Bounty
IOT Hackathon
Webinar

Join our Community

Advertise