SQL Server Error 4060: Cannot Open Database Requested by the Login

 

Introduction

SQL Server Error 4060: Cannot open database '<database_name>' requested by the login. The login failed. occurs when a user attempts to connect to a database but lacks the necessary permissions, the database does not exist, or the login is misconfigured. This guide provides code-oriented solutions to diagnose and resolve Error 4060 effectively.

---

1. Verify Database Existence

Ensure the database specified in the connection string or login exists.

Code-Oriented Solution

Check if the database exists:

-- List all databases
SELECT name
FROM sys.databases
WHERE name = 'YourDatabaseName';

Action:

• If the database is missing, restore or create it:

-- Create a new database
CREATE DATABASE YourDatabaseName;

• If the database was renamed or deleted, update the connection string to the correct database name:

// Example connection string
string connString = "Server=sqlserverhostname;Database=YourDatabaseName;Integrated Security=True;";

Why? Error 4060 occurs if the requested database does not exist on the server.

---

2. Check Login Permissions

Verify that the login has access to the database.

Code-Oriented Solution

Check if the login is mapped to the database:

-- List database users and their associated logins
USE YourDatabaseName;
GO
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.type IN ('S', 'U', 'G'); -- SQL User, Windows User, Windows Group

Action:

• If the login is not mapped, create a user for the login:

-- Create a database user for a SQL Server login
USE YourDatabaseName;
GO
CREATE USER YourUserName FOR LOGIN YourLoginName;

-- Grant CONNECT permission
GRANT CONNECT TO YourUserName;

• For Windows Authentication, ensure the Windows account or group is mapped:

-- Create user for Windows login
CREATE USER [DOMAIN\YourUserName] FOR LOGIN [DOMAIN\YourUserName];
GRANT CONNECT TO [DOMAIN\YourUserName];

Why? Error 4060 occurs if the login lacks a user mapping or CONNECT permission for the database.

---

3. Validate Default Database for the Login

Check if the login’s default database is valid and accessible.

Code-Oriented Solution

Check the login’s default database:

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

Action:

• If the default database is invalid (e.g., deleted or inaccessible), change it:

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

• Alternatively, specify the database in the connection string to override the default:

string connString = "Server=sqlserverhostname;Database=YourDatabaseName;User Id=YourLoginName;Password=YourPassword;";

Why? If the default database is inaccessible, SQL Server cannot connect the login, causing Error 4060.

---

4. Check Database State

Ensure the 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;

Why? A database in an offline, single-user, or restricted state prevents logins, triggering Error 4060.

---

5. 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

• For SQL Authentication, ensure the login exists and the password is correct:

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

Why? A mismatch between the login’s authentication type and server configuration causes login failures.

---

6. Test Connection with SQLCMD or SSMS

Use SQL Server Management Studio (SSMS) or sqlcmd to test the login.

Code-Oriented Solution

Test with sqlcmd:

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

# Test Windows Authentication
sqlcmd -S sqlserverhostname -E -d YourDatabaseName

In SSMS, connect using the login credentials and specify the database.

Action:

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

• Update the application’s connection string if needed:

using System.Data.SqlClient;

string connString = "Server=sqlserverhostname;Database=YourDatabaseName;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 with sqlcmd or SSMS isolates issues with the login or application configuration.

---

7. 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 the login:

-- 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 orphaned user
DROP USER YourUserName;

-- Recreate user
CREATE USER YourUserName FOR LOGIN YourLoginName;
GRANT CONNECT TO YourUserName;

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

---

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 4060
EXEC xp_readerrorlog 0, 1, N'4060', 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 4060 (Cannot open database requested by the login) can be resolved by verifying database existence, login permissions, default database settings, database state, and authentication mode. Use the provided T-SQL and code examples to diagnose and fix the issue systematically. 

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone