Unable to Open the Physical File (Error 5120)

The number after Operating system error is the part worth reading. It is a Windows error code, not a SQL Server one, and it is the only thing in the message that narrows anything down.

Read the Operating System Error, Not the SQL One

Three attach attempts on the same instance, each with a different underlying problem:

Errors 2 and 32 are self-explanatory. Error 5 is the one that wastes time, because it has two completely different causes and the message treats them the same.

The Trap: Access Denied Without a Permissions Problem

Verified directly. A database was created and left online, then a second attach was attempted against its own data file:

-- SalesDB is attached and running
CREATE DATABASE SalesDB_Copy
    ON (FILENAME = N'D:\SQLData\SalesDB.mdf')
    FOR ATTACH;

Nothing is wrong with the permissions. SQL Server already has that file open and is refusing to open it twice, and Windows reports that as access denied. Grant every right you like and it will not change.

So the first question for an error 5 is not “who needs permission” but “is this file already in use”. Check before touching a single ACL:

-- is SQL Server already using this file?
SELECT  DB_NAME(database_id) AS database_name,
        name AS logical_name,
        physical_name,
        state_desc
FROM    sys.master_files
WHERE   physical_name LIKE '%SalesDB%';

If a row comes back, the file belongs to a database this instance already knows about, and the answer is to detach it, restore under a different name, or stop trying to attach a copy of something that is already here.

When It Really Is Permissions

The genuine case looks identical and has a different fix. It usually happens after a human copies a file: the copy inherits the copier’s permissions, and the SQL Server service account is not the copier.

Find out who the service actually runs as, because it is often not what people assume:

SELECT  servicename,
        service_account,
        startup_type_desc,
        status_desc
FROM    sys.dm_server_services;

On the instance used for this post that returned NT Service\MSSQLSERVER, a virtual account. That is the default on a modern install, and it is exactly why a freshly copied file is unreadable: the account is local to the machine, it is not a domain user, and nobody thinks to grant it anything.

Grant the service account read and write on the folder, not on the individual file. New files created later inherit the folder permissions, so folder-level is the fix that lasts:

# from an elevated prompt, adjust the account and path
icacls "D:\SQLData" /grant "NT Service\MSSQLSERVER":(OI)(CI)F

It Never Arrives Alone

On an attach or a create, 5120 is followed by a second error:

And this is the part that matters for anything scripted: tested with all three failures inside TRY/CATCH, ERROR_NUMBER() returned 1802 every time, never 5120. Your error handler sees the useless message, not the useful one.

“Check related errors” is the documentation telling you the detail lives elsewhere. So when a job logs a bare 1802, do not go looking for a filename in it. Read the SQL Server error log, where both messages are recorded in order:

EXEC xp_readerrorlog 0, 1, N'Unable to open the physical file';

Microsoft’s reference covers Database detach and attach and sys.dm_server_services in full.

Common Questions

Related Scripts

• CREATE FILE Encountered Operating System Error (Error 5123), the sibling error, raised when creating rather than opening
• Cannot Recover the Master Database (Error 3417), what this looks like when it stops the instance starting
• How to Restore a Database in SQL Server, the safer route than attaching a loose file
• SQL Server Errors: The Complete Guide, the rest of the library