“Yesterday, when I was trying to connect with my MS SQL Server 2005, I received an error message “Cannot open user default database. Login failed”. And, it terminates the authentication process abruptly and I had no idea, what happened to my SQL Server. Thus, I want to know that, Is there any command by which I can log into the SQL Server 2005. If anyone has any suggestion regarding this situation, then kindly suggest me”.
Most of the users are facing the same SQL Server error at the time of login. So, after considering this problem, in this write-up, we will describe the easiest solution that can be applied to both SQL Server login and Windows logins too. But, first, understand Why SQL Server Error 4064 arises at the time of connection with the database.
In SQL Server, each user has a default database that is used at the time of login connection. But, due to some reasons, the default database may be dropped and no longer exists in a database. So, whenever a user provides credentials in the SQL login page and hit the connect button. The login process aborts abruptly.
There are number of possible reasons responsible for this error “cannot open user default database login failed error 4064”. Some of the major ones are listed below:
To troubleshoot the SQL Server error 4064 login failed error, a user only requires to provide an existing database name that is available in the SQL database for the connection. The motive is to replace the user’s default database that currently not available for the connection with the existing database that is present in SQL database environment. So, next time when user’s attempts to login into the MS SQL Server 2005, no error message will be displayed.
These manual solutions are applicable to SQL Server 2005 and in the earlier version. There are two solutions by which users can easily get rid of SQL Error 4064:
There is a requirement of SQLCMD utility and modify the default database in SQL Server. To do this, follow the steps that are shown below:
Note: InstanceName is equal to name of SQL Server 2005 instance to which the users are connecting. SQLLogin is equal to the name of SQL Server whose default database has been dropped. Although, Password signifies the login password of SQL Server.
ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE= AvailDBName
The Final Words
MS SQL Server error 4064 is a very familiarised error that generally occurs in almost every version of SQL Server. Therefore, in this write-up, we have discussed the reliable and easy methods that can resolve this issue nimbly.