How to Fix SQL Server Access Denied Error 5120?

The error 5120 — Access Denied is a common error that occurs when trying to attach a database in SQL Server. The complete error message reads as:

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file “C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA’ Operating system error 5: “5(Access denied.)”

The error has level 16, which means it is a problem that can be fixed at the user’s end.

In this article, we will see how to resolve the error 5120 in SQL Server.

Why does this Error Occurs?

When you attach a database, you use the physical files, like the data file with extension .mdf and the log file with .ldf extension. It is possible that SQL Server does not have enough permissions to handle the files, thus resulting in the Access Denied error.

Solutions to Fix the Error 5120 Access Denied in SQL Server

Here are some solutions you can try to resolve the error 5120 in SQL Server.

Solution 1: Open SSMS as Administrator

Sometimes, the user does not have enough privileges to attach the database. In such a case, close the SSMS and open it as an Administrator

The Run as Administrator option will provide you more privileges to attach the file.

Solution 2: Grant Privileges to the SQL Server Service to the File

Using File Explorer, navigate to the folder where the .mdf and .ldf files are stored. By default, the path is:

e:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA
Right click on the .mdf file and select Properties.

Go to the Security tab and press the Edit button.

In Permissions for database, press the Add button to add the service account.

If you do not know which account is running your service, you can check it in the SQL Server Configuration Manager in the Log On As section.

Once added in the security permissions, grant Full Control to the file.

Repeat the same steps for the log file.

Solution 3: Enable Trace Flag 1802 for MDF Files Stored on the Network

Sometimes, your data files and log files are stored in a network-attached storage.

The error message may be almost the same.

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file “\\serverSa023\sqlfiles\northwind.mdf”. Operating system error 5: (Access is denied.).

To solve this problem, go to the SQL Server Configuration Manager and double-click the SQL Server Service.

Go to the Startup Parameters tab and write -T1802 and press the Add button.

Next, press Apply and then click OK.

Finally, restart the service to enable the trace.

The trace 1802 disables the Access Control List (ACL) permissions to attach or detach databases. So, you will be able to attach the file after enabling the trace.

Solution 4: Repair MDF File

Sometimes, the error occurs if the database file is corrupted. In such a scenario, you could use a third-party tool, like Stellar Repair for MS SQL to recover the data from the damaged or corrupted file.

You can download & install the SQL repair software from here.

Once installed, take the damaged database offline in SSMS

Select the damaged .mdf data file in Stellar Repair for MS SQL and press the Repair button

After repairing, save the data in a new file.

Conclusion

In this article, we mentioned various methods to fix the error 5120 in SQL Server. First, try to run the SSMS as an administrator. If this does not work, you can check the privileges of the SQL Server Services and grant privileges to the SQL Server Service account to the mdf and ldf files to be attached. This solution is common when you detach a file and try to attach in a different server. Another way is to enable the trace flag 1802. This disables the ACL security for attachments and detachments. If the MDF is corrupted, you can download a third-party tool named Stellar Repair for MS SQL. You just need to provide a copy of the mdf file and the tool will repair it.