How to Fix Error 5120 When You Attach a Database

What is Error 5120?

Error 5120 is a database engine error with the message text Table error: Unable to open the physical file. Microsoft documents error 5120 and states that SQL Server failed to open a database file and that the Windows error in the message points to the specific reason. Error 5120 also appears together with errors 17204 or 17207.

Attach tasks with a permission problem return the message below.

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\SQLData\ClinicDB.mdf". Operating system error 5: "5(Access is denied.)".

Parts of the message and their meaning are listed below.

What Causes Error 5120?

Windows names the cause in the number that follows the text Operating system error. Four numbers appear in Microsoft guidance for open failures and the table lists them with the method that fixes every one:

Five attach conditions lead to error 5120 with these numbers:

• Windows Login Without File Access. SQL Server impersonates your Windows login during an attach to confirm access to the data and log files.
• Detach by Another Account. Microsoft states that a detach can grant file access to the connecting account only.
• SQL Server Login. A SQL Server login has no Windows account to impersonate so the service account alone receives file access.
• Moved Files. A file path that differs from the path of the last attach stops the open.
• Network Storage. SQL Server resets file permissions at detach and the share permissions can be too narrow for the next attach.

Which Facts Need a Check Before You Attach?

Seven facts need your check before you attach the database:

• Source Instance. Detach the database from the old instance first because Microsoft requires it for a move.
• Data Files. Locate every MDF and NDF file because the attach fails without all data files.
• Log File. Note whether the LDF file exists because a missing log decides the method.
• File Paths. Note the current path of every file that moved since the last attach.
• Service Account. Open Task Manager and note the user name of the sqlservr.exe process.
• Version. Confirm the destination instance uses the same or a newer SQL Server version than the source.
• Source. Execute DBCC CHECKDB on a nonproduction server first when the files come from an unknown source.

Use the statement below to confirm that no database with the same name exists on the instance.

SELECT state_desc, name, database_id
FROM sys.databases
WHERE name = N'ClinicDB';

5 Methods to Fix Error 5120 on Attach

Five methods fix error 5120 and four of them are free. Free methods need no database repair.

Compatibility needs a check before you download. Installation needs 4 GB of RAM plus 100 MB of free storage and .NET Framework version 4.5 or above. Windows 11, 10, 8 and 7 pass the check and so do Server 2016, Server 2012 R2 and Server 2008.

Method #1: Attach With a Trusted Windows Login (Free, Start Here)

Microsoft states that SQL Server impersonates a Windows login during an attach to confirm access to the data and log files. Error 5120 with error 5 disappears when the login has access to the files.

• Open SSMS and connect with Windows Authentication using an account that can open the MDF and LDF files.
• Open the shortcut menu of Databases in Object Explorer and select Attach.
• Select Add in the Attach Databases dialog box.
• Select the MDF file in the Locate Database Files dialog box and select OK.
• Check the Databases to attach grid and the database details grid for files marked Not found.
• Select OK to attach the database.

Microsoft lists the same dialog steps in its guide to attach a database.

Note. A SQL Server login gives SQL Server no Windows account to impersonate. Then the service account alone receives file access.

Method #2: Grant Access on the MDF and LDF Files (Free, Fixes Error 5)

Microsoft tells you to check the file permissions when error 5120 comes with error 5. SQL Server service accounts and groups need access to the database files.

• Find the user name of the sqlservr.exe process in Task Manager.
• Open the Properties of the MDF file and go to the Security tab.
• Select Edit and then Add.
• Enter the account from Task Manager. Default instances also accept the service SID NT SERVICE\MSSQLSERVER.
• Grant Full control and select Apply.
• Repeat the steps for the LDF file and every NDF file.
• Execute the attach again and watch for error 5120.

Note. Process Monitor from Microsoft Sysinternals shows the account behind a denied open. Look for a CreateFile operation with the result ACCESS DENIED. Our Reason 15105 Fix Guide explains messages that hide the error text.

Method #3: Correct File Paths and Rebuild a Missing Log (Free, Fixes Errors 2 and 3)

Microsoft states in the CREATE DATABASE syntax for attach that an attach needs the primary file plus an entry for every file whose path moved. Error 5120 with errors 2 or 3 points to a wrong path or a missing file.

CREATE DATABASE ClinicDB
ON (FILENAME = N'D:\SQLData\ClinicDB.mdf'),
   (FILENAME = N'E:\SQLLogs\ClinicDB_log.ldf')
FOR ATTACH;

• Replace the paths with the real location of every file.
• Include every NDF file and every log file in the statement.
• Check that your login has the CREATE DATABASE, CREATE ANY DATABASE or ALTER ANY DATABASE permission.

Use the second statement when the log file is lost.

CREATE DATABASE ClinicDB
ON (FILENAME = N'D:\SQLData\ClinicDB.mdf')
FOR ATTACH_REBUILD_LOG;

• Confirm that the database shut down cleanly and that all data files exist.
• Execute the statement and SQL Server creates a new 1 MB log file.
• Take a full backup at once because the log backup chain ends after the rebuild.

Note. ATTACH_REBUILD_LOG works only for databases that accept updates.

Method #4: Release Files in Use and Reattach From Network Storage (Free, Fixes Error 32 and Share Paths)

Microsoft names Process Explorer or Handle from Sysinternals to find the process that uses a file when error 5120 comes with error 32. Antivirus software is a common cause so Microsoft tells you to exclude the SQL Server files from scans.

• Download Process Explorer from Microsoft Sysinternals and search for the file name.
• Stop the process that uses the file.
• Exclude the SQL Server data and log files in your antivirus settings.
• Execute the attach again to confirm that error 5120 is gone.

Network storage needs more steps. Microsoft states that SQL Server resets the file permissions at detach and the next attach fails because of narrow share permissions.

• Add the startup option -T1802 to the SQL Server service in SQL Server Configuration Manager and restart the service.
• Detach the database and attach it again with the UNC paths of the files.

EXEC sp_detach_db N'ClinicDB';
GO
EXEC sp_attach_db N'ClinicDB',
    N'\\FileServer\Share\ClinicDB.mdf',
    N'\\FileServer\Share\ClinicDB_log.ldf';
GO

Note. Remove the -T1802 option after the attach works.

Method #5: Export a Damaged MDF File With EmailDoctor (Paid After Demo)

Choose recovery software when files from a failing disk refuse to attach even with correct access. EmailDoctor SQL Database Recovery extracts tables and views from damaged MDF and NDF files then lists them in a preview.

Recovery software works on the file without a successful attach. Demo mode limits every table to 25 rows and adds 5 other objects.

Open the MDF Copy and Scan It

1. Start the software once the setup finishes.
2. Choose Open on the toolbar and browse to the saved copy of ClinicDB.mdf.
3. Choose Quick Scan. Quick Scan detects the SQL Server version automatically.
4. Accept the scan summary with OK.
5. Browse the tables in the left pane to confirm the recovered rows.

Send the Objects to SQL Server

6. Go to Export and choose Export As SQL Server Database.
7. Enter the server name and the authentication type. Windows Authentication uses your account while SQL Server Authentication needs a login and password.
8. Name the new database ClinicDB_Recovered under Create New Database.
9. Tick the objects to export and set With Schema & Data.
10. Press Export and confirm the Export Completed window.
11. Verify the new database with DBCC CHECKDB (N'ClinicDB_Recovered') in SSMS and take a backup.

    Choose Advanced Scan when the Quick Scan tree shows empty tables. Our Corrupt MDF File Repair Guide lists the differences between the scan modes.

Troubleshooting Common Error 5120 Problems

Six problems slow down the fix for error 5120:

• Error 5 Returns After a Detach. The detach granted access to one account so grant the service account access with Method #2.
• Grid Shows Not Found. The path in the grid is wrong so correct it with Method #3.
• Database Exists Already. Microsoft states that attaching an attached database causes an error so check sys.databases first.
• Instance Is Older Than the Source. Per Microsoft a database created on a newer SQL Server release cannot attach to an older release.
• Broker or Trustworthy Fails After the Attach. SQL Server sets the broker options and the trustworthy option to OFF at attach so enable them again when your application needs them.
• Users Cannot Sign In. Logins and SQL Server Agent tasks need a new setup on the new instance.

Frequently Asked Questions

Author’s Verdict

Error 5120 means SQL Server cannot open a file and the Windows error in the message names the reason. Error 5 points to access while errors 2 and 3 point to paths.

Attach with a Windows login that has file access and grant the service account access to the files. Correct the paths and rebuild a missing log only when the shutdown was clean.

Use EmailDoctor recovery software when the files are damaged and no backup exists. Back up the database after every successful attach.