Ways To Fix SQL Server Attach Database Error 5120 Efficiently
Microsoft SQL Server is one of the popular and Widely used Relational database Management system. SQL language is used to manage and perform operations in the data. This application saves the data in the MDF and LDF file formats. MDF is the primary database file where the LDF file is the transaction log file. While working in the database the user encounters various issues like SQL Server Attach Database error 5120. Let us consider the problem faced by the users in one of the forum sites.
“Dear All! While attaching my .mdf file on my SQL Server database, I am getting an error like this error 5120 SQL server to attach the database. Here is the following error message. Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA’ Operating system error 5: “5(Access denied.)”. Can anyone tell the best possible solution to resolve this issue?”
In this guide, we are going to discuss the solution to the problem SQL server attach database error 5120. Also, we will the causes of this problem. Also in case if you dont be able to access SQL database then the user can take the help of SQL Recovery Tool. Let us start.
Reasons Behind SQL Server Attach Database Error 5120
The main problem arises when the SQL Server database users does not have the authorization to access the MDF and the LDF files.
In case if the MDF file is not at their accurate position then the user has to face this issue.
Not opening the SQL Server Management Studio with the admin rights.
This issue arises when the Server drives don’t have the permission to store the server files.
Know-How To Fix Error 5120 SQL Server Attach Database Issue
- First, the user has to create the two user accounts Admin1 and Admin2. After that follow the steps to resolve this problem.
CREATE DATABASE [Test] CONTAINMENT = NONE ON PRIMARY (NAME = N’Test’, FILENAME = N’D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\Test.mdf’) LOG ON (NAME = N’Test_log’, FILENAME = N’D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\Test_log.ldf’) GO
Now check the Test File Permission For MDF and LDF: After that browse the location of MDF and LDF files after that Right Click on the File »Property»Security.
Now Admin1 detaches the SQL database. To detach the database the user has to Right-click on the Test database »Tasks»Detach.
Check the file permission for MDF and LDF again: Permission has changed and the full permissions are granted to Admin1 only.
When the Admin2 tries to attach the database the SQL Server error 5120 appears because of the limited permissions.
Follow the steps to provide full permissions on both the MDF and the LDF files of the SQL Database.
- First right click on the test database and then choose Properties.
- Now click on the Security Tab and then click on the Edit button.
- After that click on the Add button and then enter the object name to NT Service\MSSQL$SQL2013.
- After clicking on Check Names and then click on OK.
- Now Give the full control to the file and after that click on OK.
Now Reattach the Database: In this way, SQL admin2 is able to reattach the database.
Note: The user can try the above steps to resolve this SQL server attach database error 5120. But in case if your MDF files are inaccessible then the user can take the help of SQL Database Recovery Software to recover data from corrupted MDF files. The user can easily recover SQL database objects such as table, stored procedure, functions. This application comes with a simple and easy to use user interface so that everyone can easily use this software with ease.
In this article, We have discussed the problem SQL server attach database error 5120. This problem can be solved by granting full permissions. But in case if your MDF file database is still inaccessible then the user can take the help of SQL Recovery Software to resolve such types of issues.
Author: Andrew Jackson