Fix 'Cannot open database' error in SQL Server fast

Simple fix (30 seconds): Check if the database exists

This is where most people should start. Open SQL Server Management Studio (SSMS) and run this:

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

Replace 'YourDBName' with your database's actual name. After you run this, look at the results. If you see your database name with 'ONLINE' in the state_desc column, the database exists and is online. If the result is empty (zero rows), the database doesn't exist on this server. That usually means you restored it to the wrong instance. Go check your server name in the connection string.

Moderate fix (5 minutes): Fix orphaned user

If the database exists and is online, the problem is often a login that doesn't match the database user. This happens after a restore from another server. The SID (security identifier) inside the database doesn't match the login's SID. Here's what to do:

1. In SSMS, right-click the database and select 'New Query'.
2. Run this command to find orphaned users:

   EXEC sp_change_users_login @Action = 'Report';

   After you run it, you'll see a list of users with mismatched logins. The most common is 'dbo' or your app user.
3. Fix the orphaned user with this:

   EXEC sp_change_users_login @Action = 'Auto_Fix', @UserNamePattern = 'YourUserName', @LoginName = 'YourLoginName';

   Replace YourUserName and YourLoginName with the names from step 2. If you're fixing the dbo user, use:

   ALTER USER dbo WITH LOGIN = sa;

   After this, try connecting again. The error should be gone.

I've seen this fix work 80% of the time after a restore. Skip the 'sp_change_users_login' if you're on SQL Server 2005 or older — it's deprecated there.

Advanced fix (15+ minutes): Check permissions and re-attach

If the above didn't work, it's time to dig deeper. The database might be there but the login doesn't have permission to access it. Here's the step-by-step for that:

Step 1: Verify login permissions

Run this in the master database context:

USE master;
GO
SELECT dp.name, dp.type_desc, dp.authentication_type_desc
FROM sys.server_principals dp
WHERE dp.name = 'YourLoginName';

If you see the login, check if it's disabled. Look for 'is_disabled' column in sys.server_principals. If it's 1, enable it with:

ALTER LOGIN YourLoginName ENABLE;

Step 2: Map the login to the database

If the login exists and is enabled, make sure it's mapped to the database. Run this in the database context:

USE YourDBName;
GO
SELECT u.name, u.principal_id, u.sid
FROM sys.database_principals u
WHERE u.name = 'YourLoginName';

If the result is empty, the login isn't mapped. Add it with:

CREATE USER YourLoginName FOR LOGIN YourLoginName;
GO
EXEC sp_addrolemember 'db_owner', 'YourLoginName';

Be careful with db_owner — only give that if it's needed. For a regular app user, use 'db_datareader' and 'db_datawriter' instead.

Step 3: Detach and re-attach the database (last resort)

If everything above checks out and it still doesn't work, the database files might be corrupted or in a weird state. Detach and re-attach them. Before you do, back up the .mdf and .ldf files. Then in SSMS:

1. Right-click the database, go to Tasks > Detach. Check 'Drop Connections' and click OK.
2. Now right-click Databases > Attach. Click Add, browse to the .mdf file, select it, and click OK.
3. Make sure both data and log files show up in the list. If the log file has issues, remove it from the list and let SQL Server create a new one. Click OK.

After the re-attach, run the orphaned user fix again. I've had this work when nothing else did.

One more thing: if you're using SQL Server Express LocalDB, the database file path might be wrong. LocalDB uses a different instance name (like (localdb)\MSSQLLocalDB). Make sure your connection string points to the right instance.

That covers the common causes. Start with the simple check, move to orphaned users, then go advanced. Most people stop at step 2 and it works.