SQL Server Error 4064: Cannot Open User Default Database at Login

 

Introduction

SQL Server Error 4064: Cannot open user default database. Login failed. occurs when a user attempts to log in to SQL Server, but their default database is inaccessible, deleted, offline, or restricted. This error prevents the login from succeeding unless resolved. This guide provides code-oriented solutions to diagnose and fix Error 4064 by addressing login settings, database availability, and user permissions.

---

1. Verify the Default Database

Check if the user’s default database exists and is accessible.

Code-Oriented Solution

Identify the login’s default database:

-- View login’s default database
SELECT 
    name AS LoginName,
    default_database_name
FROM sys.server_principals
WHERE name = 'YourLoginName';

Check if the default database exists:

-- List all databases
SELECT name, state_desc
FROM sys.databases
WHERE name = 'YourDatabaseName'; -- Replace with default database name

Action:

• If the database does not exist or is offline, proceed to Step 2 to change the default database.

• If the database exists but is inaccessible, check its state and permissions (Steps 3 and 4).

Why? Error 4064 occurs if the default database is missing, offline, or inaccessible.

---

2. Change the Default Database

Set a valid default database (e.g., master) for the login.

Code-Oriented Solution

Modify the login’s default database:

-- Set default database to master
ALTER LOGIN YourLoginName
WITH DEFAULT_DATABASE = master;

Action:

• Verify the change:

SELECT 
    name AS LoginName,
    default_database_name
FROM sys.server_principals
WHERE name = 'YourLoginName';

• Test the login after the change (see Step 6).

Why? Setting a default database like master, which is always available, bypasses the error.

---

3. Check Database State

Ensure the default database is online and not in a restricted state.

Code-Oriented Solution

Check the database state:

-- Check database state
SELECT 
    name,
    state_desc,
    user_access_desc
FROM sys.databases
WHERE name = 'YourDatabaseName';

Action:

• If the database is offline, bring it online:

ALTER DATABASE YourDatabaseName SET ONLINE;

• If in single-user or restricted mode, set to multi-user:

ALTER DATABASE YourDatabaseName SET MULTI_USER;

• If the database is in recovery or suspect mode, investigate recovery:

-- Attempt to repair (use with caution)
ALTER DATABASE YourDatabaseName SET EMERGENCY;
DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE YourDatabaseName SET ONLINE;

Why? A database that is offline, in single-user mode, or in recovery prevents login access, causing Error 4064.

---

4. Verify User Permissions

Ensure the login has access to the default database.

Code-Oriented Solution

Check if the login is mapped to a user in the database:

USE YourDatabaseName;
GO
-- List database users and their logins
SELECT 
    dp.name AS DatabaseUser,
    sp.name AS ServerLogin
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.name = 'YourUserName';

Action:

• If no user exists, create one and grant CONNECT permission:

-- Create database user
USE YourDatabaseName;
GO
CREATE USER YourUserName FOR LOGIN YourLoginName;
GRANT CONNECT TO YourUserName;

• For Windows Authentication, map the Windows account:

CREATE USER [DOMAIN\YourUserName] FOR LOGIN [DOMAIN\YourUserName];
GRANT CONNECT TO [DOMAIN\YourUserName];

Why? Lack of a database user or CONNECT permission prevents access to the default database.

---

5. Check for Orphaned Users

If the database was restored or attached, the user may be orphaned (SID mismatch).

Code-Oriented Solution

Check for orphaned users:

USE YourDatabaseName;
GO
-- List orphaned users
EXEC sp_change_users_login @Action = 'Report';

Action:

• Fix orphaned users by re-mapping:

-- Map database user to server login
EXEC sp_change_users_login @Action = 'Update_One', 
    @UserNamePattern = 'YourUserName', 
    @LoginName = 'YourLoginName';

• Alternatively, drop and recreate the user:

-- Drop and recreate user
DROP USER YourUserName;
CREATE USER YourUserName FOR LOGIN YourLoginName;
GRANT CONNECT TO YourUserName;

Why? Orphaned users after a database restore or attach prevent login access.

---

6. Test Connection with SQLCMD or SSMS

Test the login to confirm the resolution.

Code-Oriented Solution

Test with sqlcmd:

# Test SQL Server Authentication
sqlcmd -S sqlserverhostname -U YourLoginName -P YourPassword

# Test Windows Authentication
sqlcmd -S sqlserverhostname -E

In SSMS, connect using the login credentials and specify the server (without a database).

Action:

• If the connection fails, note the exact error message for further diagnosis.

• Update the application’s connection string to specify a valid database:

using System.Data.SqlClient;

string connString = "Server=sqlserverhostname;Database=master;User Id=YourLoginName;Password=YourPassword;";
try {
    using (SqlConnection conn = new SqlConnection(connString)) {
        conn.Open();
        Console.WriteLine("Connection successful!");
    }
} catch (SqlException ex) {
    Console.WriteLine($"Error: {ex.Message}");
}

Why? Testing confirms whether the login can access SQL Server after fixing the default database.

---

7. Verify Authentication Mode

Ensure the server supports the authentication mode used by the login.

Code-Oriented Solution

Check the server’s authentication mode:

-- Check authentication mode
SELECT 
    SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsWindowsAuthOnly;
-- Returns 1 for Windows Authentication only, 0 for Mixed Mode

Action:

• If using SQL Server Authentication and the server is Windows-only, enable Mixed Mode:

-- Enable Mixed Mode
EXEC xp_instance_regwrite 
    @rootkey = N'HKEY_LOCAL_MACHINE',
    @key = N'Software\Microsoft\MSSQLServer\MSSQLServer',
    @value_name = N'LoginMode',
    @value = 2;

-- Restart SQL Server service

• Verify the login exists for SQL Authentication:

-- Create or verify SQL Server login
CREATE LOGIN YourLoginName WITH PASSWORD = 'YourSecurePassword';

Why? A mismatch between the login’s authentication type and server configuration can exacerbate login issues.

---

8. Analyze SQL Server Logs

Check logs for additional details on the login failure.

Code-Oriented Solution

Query the error log:

-- Read error log for Error 4064
EXEC xp_readerrorlog 0, 1, N'4064', NULL, NULL, NULL, N'asc';

Action:

• Review logs for details about the login attempt or database state.

• Check the log file manually at: C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Why? Logs may reveal specific issues, such as permission denials or database state problems.

---

Conclusion

SQL Server Error 4064 (Cannot open user default database) can be resolved by setting a valid default database, ensuring the database is online, verifying permissions, and fixing orphaned users. Use the provided T-SQL and code examples to diagnose and resolve the issue systematically. 

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone