SQL Server "Cannot open database requested by the login" (error 4060): what to check

1. Is the name right?

It’s the most common reason, and connection strings are the place to look: Initial Catalog=shop or Database=shop. Is it correct for this environment? List what exists:

SELECT name, state_desc, recovery_model_desc FROM sys.databases;

Names are case-insensitive on most servers, but not on all. A server with a case-sensitive collation treats Shop and shop as different. A typical sign is that a new deploy points at a database that hasn’t been created yet.

2. Is it online?

SELECT name, state_desc, user_access_desc FROM sys.databases WHERE name = 'shop';

If user_access_desc is SINGLE_USER, then someone else holds the only slot, usually an old tool or the application. Find them, and open it up again:

SELECT session_id, login_name, host_name, program_name
FROM sys.dm_exec_sessions WHERE database_id = DB_ID('shop');

ALTER DATABASE shop SET MULTI_USER;

3. Does the login have a user?

SQL Server has two levels: a login gets you into the server, and a user gets you into one database. A login with no user in that database gets exactly this error.

USE shop;
SELECT name, type_desc FROM sys.database_principals WHERE name = 'app';

No row? Then add one, and give it rights:

USE shop;
CREATE USER app FOR LOGIN app;
ALTER ROLE db_datareader ADD MEMBER app;
ALTER ROLE db_datawriter ADD MEMBER app;

4. After a restore or a move

This is the classic: it worked on the old server. A user inside a database points to its login by an ID, and the login on the new server has another ID. The names match, and the link doesn’t, so it’s known as an orphaned user. Find them:

USE shop;
SELECT dp.name AS orphaned_user
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type = 'S' AND sp.sid IS NULL AND dp.authentication_type_desc = 'INSTANCE';

Fix the link, and there’s no need to delete anything:

ALTER USER app WITH LOGIN = app;

To avoid this on a move, script the logins from the old server with their SIDs, using Microsoft’s sp_help_revlogin.

5. The login’s default database

Even with no database in your connection string, you can get this. Each login has a default database, and if that’s gone, then logging in fails, even for tools like SSMS:

SELECT name, default_database_name FROM sys.server_principals WHERE name = 'app';
ALTER LOGIN app WITH DEFAULT_DATABASE = master;

If you’re locked out by this, tell your tool to connect to master explicitly. In SSMS it’s under Options → Connection Properties → Connect to database, and with sqlcmd, use -d master.

6. Azure SQL

On Azure SQL Database, you can’t USE to change databases, and the firewall has a similar message for clients it doesn’t allow. Connect to the database itself, and create a contained user there:

CREATE USER app WITH PASSWORD = 'new-password-here';

Cannot open server "x" requested by the login is the firewall rule, so add your address under the server’s networking settings.