Fix 'Cannot open database' on SQL Server 2019 – 3 real fixes

Quick answer: Run SELECT name, state_desc FROM sys.databases WHERE name = 'YourDB' to check if it's online. If not, use ALTER DATABASE YourDB SET ONLINE. Then check the user mapping or run sp_change_users_login 'Auto_Fix', 'YourUser' if it's an orphaned user.

I know this error is infuriating — you're staring at a blank connection screen, and the app just refuses to talk to the database. I've been there, and in 6 years running a help desk blog, this was one of the top 5 SQL Server issues. The error "Cannot open database" (sometimes followed by "requested by the login") has three main triggers: the database is offline, the database doesn't exist on that server, or the login mapping is broken. Let's fix it for real.

Why this happens

This error pops up when SQL Server can't find or access the database you're connecting to. Common scenarios:

• You restored a backup from another server but forgot to bring the user along — orphaned user.
• The database was set to OFFLINE for maintenance and nobody brought it back.
• Someone typed the database name wrong in the connection string (yes, this happens).
• The login has no permission to the database at all.

Fix 1: Verify the database exists and is online

Open SQL Server Management Studio (SSMS) and run this query against the master database:

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

If you get no rows back, the database doesn't exist on that server. Check you're connecting to the right instance. If it returns OFFLINE, bring it online:

ALTER DATABASE YourDatabaseName SET ONLINE;

If the database is in SUSPECT mode, that's a bigger problem — you might need to restore from backup. But 80% of the time it's just offline.

Fix 2: Fix orphaned users

This is the most common culprit when you move a database between servers. The login exists at the server level, but it's not linked to the database user. Run this against your database (not master):

EXEC sp_change_users_login 'Auto_Fix', 'YourUserName';

This remaps the login. You'll see a message like "The row for user 'YourUserName' was fixed." After that, reconnect.

If you're on SQL Server 2016 or later (including 2019), use the newer ALTER USER approach:

ALTER USER YourUserName WITH LOGIN = YourUserName;

Fix 3: Grant database access to the login

Maybe the login simply never had access. In SSMS, right-click the database > Properties > Permissions. Or run this:

USE YourDatabaseName;
CREATE USER YourUserName FOR LOGIN YourUserName;
EXEC sp_addrolemember 'db_datareader', 'YourUserName';
EXEC sp_addrolemember 'db_datawriter', 'YourUserName';

Adjust roles as needed — your app might need db_owner if it creates tables, but don't over-permission.

Alternative fixes if the main ones fail

• Check the connection string: make sure Initial Catalog=YourDatabaseName matches the actual database name. Case matters if your server is case-sensitive.
• Check if the database is in single-user mode: run SELECT name, user_access_desc FROM sys.databases WHERE name = 'YourDB'. If it says SINGLE_USER, someone else is connected. You can force it to multi-user: ALTER DATABASE YourDB SET MULTI_USER WITH ROLLBACK IMMEDIATE.
• Check the SQL Server error log: look for entries around the time of failure. Sometimes the database is corrupted or the file path changed.

Prevention tip

Before restoring a database to a new server, always script out the logins from the old server using sp_help_revlogin (Microsoft's stored procedure) or use the Transfer Logins task in SSMS. This prevents orphaned users entirely. Also, never take a database offline without a scheduled task to bring it back — I've seen production databases sit offline for days because someone forgot.

That's it. You should be back in business. If one of these steps worked for you, or if you hit a different variation, drop a comment below — I read every single one.