Generally, SQL throws the error 4064 because while trying to connect to the SQ, the default database is not available to complete the connection.
As a part of the SQL Server specialists team, we assist our clients in fixing such issues related to SQL.
Let's state what our technicians say about possible causes and fixes.
What is exactly SQL server error 4064?
Each SQL user has a default database used for the connection when no explicit database is specified. When no database is available to make the SQL connection (including default), it throws 4064.
What are the general causes of error 4064?
- If the default database is dropped, the above error will throw when connecting the SQL. This generally happens when the user's default database is offline.
- If the default database is in Single User Mode and the available connection is already in use.
- If the default database is detached.
- If the default database is set to the RESTRICTED_USER mode.
- Or if the default database does not contain the user's login account or access is denied.
Here is the answer provided by our specialist technicians team to resolve such issues.
- Using SQL Server Management Studio.
- Open the SQL Server Management Studio and click on the Connect database Engine. Then, enter the server name and the login credentials.
- Next, click on the options tab. Next, you can see the connect to the database is set to the default database.
- Now, change this setting to any accessible database.
- Next, click on the "Connect" button to connect the SQL.
2. Change the default database of SQL User.
- Once you find the user's default database, the next step is to change the default database of that user.
- Typically there are two methods to perform the same. T-SQL code and GUI. Here, we have provided the steps with the GUI.
- Expand the Security folder—next, double-click on the SQL username.
- Here, you can see the default database is blank; this is the main issue because your database is dropped from the instance.
- Now, replace it with the master database and click on the "Ok" button.
- Next, change the connection database to any existing database, e.g., master or msdb.
Another method is to use Alter command to set the default database of the SQL user.
ALTER LOGIN [SQLUSER] WITH DEFAULT_DATABASE = master
Replace "SQLUSER" with the username you are using for the login to SQL.