SQL Error 4060: Cannot open database requested by the login

 SQL Server Error 4060 indicates that the login attempt to a specific database has failed. The full error message typically looks like this:

Cannot open database "DatabaseName" requested by the login. The login failed. Login failed for user 'UserName'.

Common Causes and Solutions

1. Database Does Not Exist

Cause: The database specified in the connection string does not exist on the SQL Server instance.

Solution: Verify that the database name in the connection string is correct and that the database exists on the server.

SELECT name FROM sys.databases WHERE name = 'DatabaseName';

2. Insufficient Permissions

Cause: The login does not have the necessary permissions to access the database.

Solution: Grant the appropriate permissions to the user.

USE master;
GRANT CONNECT TO [UserName];
USE [DatabaseName];
ALTER USER [UserName] WITH LOGIN = [UserName];

3. User Not Mapped to Database

Cause: The login is not mapped to a user in the database.

Solution: Map the login to a user in the database.

USE [DatabaseName];
CREATE USER [UserName] FOR LOGIN [UserName];

4. Database in Single-User Mode

Cause: The database is in single-user mode and another user is already connected.

Solution: Change the database to multi-user mode.

ALTER DATABASE [DatabaseName] SET MULTI_USER;

5. Database is Offline

Cause: The database is set to offline mode.

Solution: Set the database to online mode.

ALTER DATABASE [DatabaseName] SET ONLINE;

6. Login with Default Database Issue

Cause: The login’s default database is not accessible or does not exist.

Solution: Change the default database for the login.

ALTER LOGIN [UserName] WITH DEFAULT_DATABASE = [ExistingDatabase];

Example Troubleshooting Steps

Here is a structured approach to troubleshoot SQL Server Error 4060:

• Verify Database Existence: Check if the database exists on the server.

SELECT name FROM sys.databases WHERE name = 'DatabaseName';

• Check User Mapping: Ensure the login is mapped to a user in the database

USE [DatabaseName];
SELECT name FROM sys.database_principals WHERE name = 'UserName';

• Check Database Status: Verify that the database is online and in multi-user mode.

SELECT state_desc FROM sys.databases WHERE name = 'DatabaseName';

        To bring the database online and set it to multi-user mode if necessary:

ALTER DATABASE [DatabaseName] SET ONLINE;
ALTER DATABASE [DatabaseName] SET MULTI_USER;

• Grant Necessary Permissions: Ensure the login has the necessary permissions.

USE master;
GRANT CONNECT TO [UserName];
USE [DatabaseName];
ALTER USER [UserName] WITH LOGIN = [UserName];

• Check Default Database: Verify and change the default database for the login if required.

ALTER LOGIN [UserName] WITH DEFAULT_DATABASE = [ExistingDatabase];

Example Scenario

Suppose you encounter the error message while trying to connect to a database named SalesDB with a user named SalesUser. Follow these steps:

• Check if SalesDB exists:

SELECT name FROM sys.databases WHERE name = 'SalesDB';

• Check user mapping in SalesDB:

USE [SalesDB];
SELECT name FROM sys.database_principals WHERE name = 'SalesUser';

• Check the status of SalesDB:

SELECT state_desc FROM sys.databases WHERE name = 'SalesDB';

        If the database is offline, bring it online:

ALTER DATABASE [SalesDB] SET ONLINE;

• Ensure SalesUser has access to SalesDB:

USE master;
GRANT CONNECT TO [SalesUser];
USE [SalesDB];
ALTER USER [SalesUser] WITH LOGIN = [SalesUser];

• Check and set the default database for SalesUser:

ALTER LOGIN [SalesUser] WITH DEFAULT_DATABASE = [SalesDB];

By following these steps, you should be able to diagnose and resolve the SQL Server Error 4060, ensuring that the login can successfully access the requested database.