SQL Server Database Not Accessible? Fix It in 6 Ways

What Does the Database Not Accessible Error Mean in SQL Server?

Object Explorer refuses to open a database when the database cannot accept your connection. Three conditions cause the refusal.

• Database State. SQL Server opens a database for normal use in the ONLINE state only.
• Access Mode. The SINGLE_USER and RESTRICTED_USER modes limit who can connect.
• Login Permission. Logins without a database user fall back to the guest user and the guest user needs the CONNECT permission.

What Causes a Database to Become Not Accessible?

Six causes make a database not accessible.

• NORECOVERY Restore. A restore with NORECOVERY puts the database in the RESTORING state so you can apply more backups.
• Manual Offline Action. An administrator action sets the OFFLINE state and the database remains offline until someone brings it back.
• Missing Files or Resources. Error 945 says SQL Server cannot open the database because of inaccessible files or too little memory or disk space.
• Damaged Primary Filegroup. SQL Server marks the database SUSPECT when it cannot recover the primary filegroup during startup.
• Access Mode Limit. A database in SINGLE_USER mode accepts one connection and a database in RESTRICTED_USER mode accepts only privileged logins.
• Missing Login Access. A login without a database user receives the permissions of the guest user.

How to Find Out Why the Database is Not Accessible?

Execute the query below in a New Query window. Its state_desc column shows the state and its user_access_desc column shows the access mode.

SELECT name, state_desc, user_access_desc
FROM sys.databases
ORDER BY name;

Every row below describes a database that is not accessible for a different reason. Match your result with the row.

RECOVERING is a transient state. Databases become ONLINE when the recovery succeeds and SUSPECT when it fails.

What to Check Before You Start the Fix?

Gather the files and the evidence first.

• Copy the database files (MDF, NDF and LDF) to a separate folder.
• Open the SQL Server error log and save the first error that names the database.
• Confirm that your login belongs to the sysadmin role.
• Locate the latest full backup and every log backup that follows it.
• Check the free space on the disk that stores the database files.

Which Free Methods Fix SQL Server Database Not Accessible Errors?

Five free methods fix a SQL Server database that users cannot open. Start with the method that matches your query result.

Recover a Restoring Database (Free)

Users cannot open a RESTORING database and SSMS reports it as not accessible. Databases show RESTORING after a restore with NORECOVERY and while a restore is executing.

1. Confirm that no restore is executing and that you applied every backup you need.
2. Execute the command below in a query window. SQL Server recovers the database and restores no backup because the command has no FROM clause.

RESTORE DATABASE [InventoryDB] WITH RECOVERY;

3. Refresh Object Explorer and expand the database.

Recovery ends the restore sequence so SQL Server cannot apply more backups to the database.

Set the Database Online (Free)

OFFLINE and RECOVERY_PENDING databases are not accessible until someone brings them online. OFFLINE databases need only the command in step 3.

1. Open the SQL Server error log and find the first error. Microsoft names inaccessible files, too little memory and too little disk space as the causes of error 945.
2. Confirm the folder of the MDF and NDF files. Give the Database Engine service account permission to the files.
3. Use the command below.

ALTER DATABASE [InventoryDB] SET ONLINE;

4. Execute the query from the previous section to confirm the ONLINE state.

Moving a database from OFFLINE to ONLINE needs the ALTER ANY DATABASE permission on the server.

Reset the Access Mode (Free)

SINGLE_USER mode accepts one connection and the mode remains after the login that set it signs out. Another application or query window can use that one connection. RESTRICTED_USER mode accepts members of the db_owner role, the dbcreator role and the sysadmin role.

1. Close other query windows and applications that use the database.
2. Connect with a sysadmin login and open a New Query window on the master database.
3. Execute the commands below. WITH ROLLBACK IMMEDIATE rolls back incomplete transactions immediately so the command does not wait.

USE master;
ALTER DATABASE [InventoryDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Fix Login Access (Free)

Databases are not accessible to logins without a database user. Error 916 says The server principal is not able to access the database under the current security context. Logins without a user in the database use the guest user and the guest user has no CONNECT permission.

1. Connect with a sysadmin login.
2. Create a database user for the login with the commands below.

USE [InventoryDB];
CREATE USER [CONTOSO\Larry] FOR LOGIN [CONTOSO\Larry];

3. Add the user to the database roles your work needs. Microsoft says a new user gets access to the database and gets no access to the objects in it.
4. Execute the commands below when the error names msdb.

USE msdb;
GRANT CONNECT TO [CONTOSO\Larry];

Regain Sysadmin Access

Start the instance in single user mode when no sysadmin login works. Members of the local Administrators group connect as sysadmin in this mode.

1. Stop SQL Server Agent because Agent takes the only connection.
2. Open Command Prompt with administrator rights and stop the SQL Server service with the command below.

net stop "SQL Server (MSSQLSERVER)"

3. Start the instance with the next command and replace MSSQLSERVER with your instance name.

net start "SQL Server (MSSQLSERVER)" /m"Microsoft SQL Server Management Studio - Query"

4. Connect through a New Query window in SSMS and not through Object Explorer. Object Explorer needs more than one connection.
5. Add your login to the sysadmin role with the command below.

ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\Larry];

6. Stop the service again and start it without the /m option.

net stop "SQL Server (MSSQLSERVER)"
net start "SQL Server (MSSQLSERVER)"

Open the Database in Emergency Mode (Free, Sysadmin Only)

SSMS reports SUSPECT databases as not accessible. Microsoft built the EMERGENCY state to give administrators access to a suspect database.

Emergency mode marks the database READ_ONLY and disables logging. Only sysadmin members can use the database.

1. Duplicate the MDF, NDF and LDF files into a separate folder.
2. Execute the command below.

ALTER DATABASE [InventoryDB] SET EMERGENCY;

3. Refresh Object Explorer and expand the database.
4. Copy your tables to a new database with the commands below. SELECT INTO copies columns and rows and does not copy indexes or constraints.

CREATE DATABASE [InventoryDB_Copy];
SELECT * INTO [InventoryDB_Copy].dbo.Products FROM [InventoryDB].dbo.Products;

5. Repair the database with REPAIR_ALLOW_DATA_LOSS only when SQL Server cannot copy a table.

Our SQL Database Integrity Check Failed guide lists the repair commands. Emergency repair steps appear in the Fix SQL Database Error 1813 article.

How to Fix a Database That is Not Accessible With EmailDoctor Software?

Use EmailDoctor SQL Database Recovery when the database is still not accessible after the free methods. Copy the MDF and NDF files to another folder and scan the copies. Software opens the MDF files of databases that are not accessible.

Free demo versions show every recovered component in the preview. Exports stop after 25 records in a table and after 5 other database objects.

Software adds five features for damaged databases.

• Scan Modes. Quick Scan handles light damage and Advanced Scan handles severe corruption.
• Version Detection. The software finds which SQL Server version created the MDF file.
• NDF Files. Add the NDF files of the database or let the software detect them.
• Deleted Records. The preview shows deleted records in red.
• Export Formats. Export to CSV or to a SQL script or to a new database on your server.

Confirm the system needs before installation.

• Allow 100 MB of free disk space and 4 GB of RAM.
• Install .NET Framework 4.5 or higher.
• Install the software on 32 bit or 64 bit Windows 7, 8, 10 and 11. Windows Server 2008, 2012 R2 and 2016 work too.
• Files created by SQL Server 2000 through 2019 open in the software.

Scan the MDF File

1. Start the application from the Windows Start menu.
2. Click Open and locate the MDF file.
3. Choose Quick Scan and tick Auto detect to let the software find the SQL Server version. You can select the version from the list instead.
4. Go to the NDF Options tab and select either option. Option one detects the NDF files automatically and option two adds them with Add Folder or Add Files. Confirm with OK.
5. Wait until the software finishes the scan and shows the scan report.
6. Close the Quick Scan note with OK.

Preview the Recovered Objects

7. Expand the folders in the left panel and click a table to view its records.

Export to a New SQL Server Database

8. Choose Export As SQL Server Database from the Export options.
9. Enter the server name or choose it from the list. Sign in with your Windows account or with a SQL Server login by selecting Windows Authentication or SQL Server Authentication.
10. Choose Create New Database as the destination.
11. Name the new database.
12. Use the default collation or choose a different collation in the list box. Tick the components you need.
13. Select With Schema & Data and click Export.
14. Confirm the Export Completed message with OK and close the status window with Close.
15. Refresh the databases in SSMS and expand the new database to confirm that it opens.

    Select Advanced Scan when Quick Scan shows too few tables. Compare the two scan modes in Repair Corrupt SQL MDF File.

How to Choose Between the Six Methods?

Compare the six methods that fix a database not accessible in SSMS.

Match the state first and use the method from the table. Choose recovery software when every free method fails.

What Problems Appear While You Fix the Error?

Expect the problems below during the fix.

• SET ONLINE Fails. Fix the file path or the disk space or the file permission that error 945 names and execute the command again.
• Command Waits for Users. Add WITH ROLLBACK IMMEDIATE because a command without a termination clause waits for other sessions to finish.
• Message Remains in Object Explorer. Refresh Object Explorer and connect again when SSMS still shows the database as not accessible after the state shows ONLINE.
• No Sysadmin Login Exists. Start the instance in single user mode and add your login to the sysadmin role.
• Availability Group Database. Microsoft says single user startup does not start availability groups so remove the database from the group first.
• Agent Takes the Connection. Stop SQL Server Agent before you connect to the single user instance.

Our Suspect Database in SQL Server guide explains the SUSPECT state in more detail.

Frequently Asked Questions

Final Words

SQL Server reports a database as not accessible when the state is not ONLINE or the access mode limits you or your login has no access. Query sys.databases first because the result names the cause.

Back up the database files before any fix. Start with the free commands and save the repair option for the last step. Recovery software suits databases that remain not accessible after the free commands.

EmailDoctor software opens the MDF file of a database and saves the recovered items in a new database or a script or a CSV file.