Fix 'Cannot open database' error in SQL Server

1. The database is in RECOVERY_PENDING or SUSPECT mode

This is the most common cause I see in help desk tickets. Someone restarted the SQL Server service while a database was mid-recovery, or a log file got corrupted. The database shows up in SSMS but has a grey icon or says "(Recovery Pending)" next to it.

Here's what to do:

1. Open SQL Server Management Studio (SSMS) and connect to the instance.
2. Run this query to check the database state:
   

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

3. If it shows RECOVERY_PENDING or SUSPECT, you need to bring it back manually.
4. First, set the database to SINGLE_USER mode to kick out any hung connections:
   

   ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

5. Then set it to EMERGENCY mode:
   

   ALTER DATABASE YourDatabaseName SET EMERGENCY;

6. Run DBCC CHECKDB to find corruption:
   

   DBCC CHECKDB('YourDatabaseName');

7. If there's corruption, repair it with:
   

   DBCC CHECKDB('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);

8. After repair, set the database back to MULTI_USER and ONLINE:
   

   ALTER DATABASE YourDatabaseName SET MULTI_USER;
   ALTER DATABASE YourDatabaseName SET ONLINE;

After step 8, you should see the database come online. The error stops immediately. If the database won't recover even after repair, restore from your latest backup. That's your safety net.

2. Orphaned database user — login exists but has no mapping

This one's tricky. The user account you're connecting with is valid in SQL Server, but SQL Server can't match it to a user in the database. This happens after restoring a database from a different server or when you drop and recreate a login with the same name but a different SID.

Here's how to spot it:

1. Connect to SQL Server with a sysadmin account (like sa).
2. Run this to see orphaned users:
   

   EXEC sp_change_users_login @Action='Report';

3. If you see your user listed, fix it by linking the login back to the database user:
   

   EXEC sp_change_users_login @Action='update_one', @UserNamePattern='YourUserName', @LoginName='YourUserName';

Alternatively, you can use ALTER USER for SQL Server 2012 and later:

ALTER USER YourUserName WITH LOGIN = YourUserName;

After running that, try connecting again. The error should be gone. I've seen this fix work for 90% of orphaned user cases.

3. The database is in OFFLINE or RESTRICTED_USER mode

Less common but pops up when someone manually took the database offline or set it to single user to run maintenance and forgot to switch it back. You'll get the error when any app tries to connect.

Check the database state:

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

If state_desc shows OFFLINE, bring it online:

ALTER DATABASE YourDatabaseName SET ONLINE;

If user_access_desc shows RESTRICTED_USER, set it to MULTI_USER:

ALTER DATABASE YourDatabaseName SET MULTI_USER;

Don't forget to check if the database is set to READ_ONLY too. If it is, run:

ALTER DATABASE YourDatabaseName SET READ_WRITE;

Once you run these commands, the database should be accessible. Test with a simple SELECT 1 query in SSMS to confirm.

Quick-reference summary table

Try these in order. Start with the database state check — it's the fastest to diagnose. If the state is fine, move to orphaned users. I've never needed to go further than that for 95% of the cases I've handled.