The SQL error 5120 is a common error that is triggered when the account that runs the SQL service does not have the permission to use MDF files of a database.
As a part of our Server Management Services, we help our customers to fix MS SQL errors such as this one regularly.
Let’s today discuss the possible causes and fixes for this error.
What causes SQL error 5120?
SQL error 5120 triggers while attaching databases. It happens due to a number of reasons. A typical error message looks like:
The possible reasons for this error include:
1. Storage of MDF files in an inaccurate location: The main reason for error 5120 is the storage of MDF database files in an inaccurate location. It means that the MDF file is stored in some other drive of a computer.
2. Insufficient permission for system drives to store server file: System drives might not be having permission to store the server file within them. Thus, when a user tries to connect database files to the server, it results in the occurrence of SQL server 5120 messages.
Apart from these common reasons, some alternate reasons like not opening SQL Server Management Studio with admin right, not using Windows Authentication account, etc.
Let us now look at the possible solutions to fix this error.
How to fix SQL error 5120?
As we discussed earlier, the primary reason for this error message is insufficient permission to attach or detach the database. Thus the method to fix it include changing the permission of the file or moving the files to another directory.
Add permission to the drive
To add the required permission to the file, we need to
1. First right-click on the MDF or LDF file and click on properties.
2. From the Security tab, click on the user.
3. Now change the permission to full control and click on OK.
This will fix the issue.
At times, granting full permission may not fix the error 5120. Major problems within your SQL database can trigger this error. In that condition, either restore entire data from SQL Backup or a repair of the corrupted SQL Database will be required.
[Need help to fix SQL errors? We are available 24×7]
Conclusion
In short, the error 5120 triggers due to the absence of permissions or due to a wrong storage location of database log files. Today, we discussed how our Support Engineers fixes this error.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;
While attaching my .mdf file on my SQL Server database I am getting below error message:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file
«C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA’
Operating system error 5: «5(Access denied.)».
I don’t have my .ldf file of associated database. How can I attach my .mdf file without .ldf file? I am using SQL Server 2014.
AlexMelw
2,33625 silver badges35 bronze badges
asked Dec 25, 2015 at 5:09
5
GO to the folder where you have stored MDF file. You can either do this to the entire folder or the MDF/LDF individually. Select file/folder-> Right click ->click on «Security». Then «Edit’. If you dont have OWNER RIGHTS listed Click ADD and search for it, click OK.
And then give Full Control to user OWNER RIGHTS.
permission for mdf file
answered Feb 27, 2017 at 21:06
maneeshmaneesh
8997 silver badges6 bronze badges
2
That error is telling you that the account that is running the SQL Server service doesn’t have permission to the mdf file. Fix the permissions and it should attach just fine.
Pro tip: pictures of text aren’t as useful as the actual text. 
answered Dec 25, 2015 at 8:02
Ben ThulBen Thul
30.9k4 gold badges45 silver badges68 bronze badges
1
Run SQL Server Management Studio as Administrator to attach the .MDF file. After that, no need to run SSMS as Administrator any more.
answered Aug 20, 2020 at 17:10
JimmyJimmy
611 silver badge1 bronze badge
answered May 26, 2019 at 10:42
PobaranchukPobaranchuk
8399 silver badges13 bronze badges
Using SQL Server 2008R2 Express.
My db is a .mdf, 9GB in size. The .ldf is 23gb in size.
I am trying to attach my .mdf to SQL Server 2008R2 Express. The DB was take from our live environment. I am working in a test environment with 2008R2 Express.
When try attach — receive error:
___________________________________________________
Attach database failed for server «myserver/name». (Microsoft.SqlServer.Smo)
Additional information:
-> An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
-> Unable to open the physical file «e:Myfilename». Operating system error 2: «2(The system cannot find the file specified)». (Microsoft SQL Server, Error 5120)
___________________________________________________
I do not know why «cannot find the file specified» it is sitting right there on the same HDD as my SQL installation — User has full control to files an folders.
I need to attach this mdf! Is my .ldf too large or 2008R2 express?
Thanks for your help!
| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords |
|---|---|---|---|---|---|---|---|---|
|
MSSQLSERVER_5120 |
MSSQLSERVER_5120 |
pijocoder |
jopilov |
07/25/2020 |
sql |
supportability |
reference |
5120 (Database Engine error) |
MSSQLSERVER_5120
[!INCLUDE SQL Server]
Details
| Attribute | Value |
|---|---|
| Product Name | SQL Server |
| Event ID | 5120 |
| Event Source | MSSQLSERVER |
| Component | SQLEngine |
| Symbolic Name | DSK_FCB_FAILURE |
| Message Text | Table error: Unable to open the physical file «%.*ls». Operating system error %d: «%ls». |
Explanation
[!INCLUDEssNoVersion] was unable to open a database file. The operating system error provided in the message points to more specific underlying reasons for the failure. You may commonly see this error together with other errors like 17204 or 17207.
User action
Diagnose and correct the operating system error, then retry the operation. There are multiple states that can help Microsoft narrow down the area in the product where the area is occurring.
Access is denied
If you are getting the Access is Denied operating system error = 5, consider these methods:
-
Check the permissions that are set of the file by looking at the properties of the file in Windows Explorer. [!INCLUDEssNoVersion] uses Windows groups to provision Access Control on the various file resources. Make sure the appropriate group [with names like SQLServerMSSQLUser$ComputerName$MSSQLSERVER or SQLServerMSSQLUser$ComputerName$InstanceName] has the required permissions on the database file that is mentioned in the error message. Review Configure File System Permissions for Database Engine Access for more details. Ensure that the Windows group actually includes the [!INCLUDEssNoVersion] service startup account or the service SID.
-
Review the user account under which the [!INCLUDEssNoVersion] service is currently running. You can use the Windows Task Manager to get this information. Look for the «User Name» value for the executable «sqlservr.exe». Also if you recently changed the [!INCLUDEssNoVersion] service account, know that the supported way to do this operation is to use the SQL Server Configuration Manager utility.
-
Depending on the type of operation (opening databases during server startup, attaching a database, database restore, and so on), the account that is used for impersonation and accessing the database file may vary. Review the topic Securing Data and Log Files to understand which operation sets what permission and to which accounts. Use a tool like Windows SysInternals Process Monitor to understand if the file access is happening under the security context of the [!INCLUDEssNoVersion] instance service startup account [or Service SID] or an impersonated account.
If [!INCLUDEssNoVersion] is impersonating the user credentials of the login that executes the ALTER DATABASE or CREATE DATABASE operation, you will notice the following information in the Process Monitor tool (an example).
Date & Time: 3/27/2010 8:26:08 PM Event Class: File System Operation: CreateFile Result: ACCESS DENIED Path: C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLDATAattach_test.mdf TID: 4288 Duration: 0.0000366 Desired Access:Generic Read/Write Disposition: Open Options: Synchronous IO Non-Alert, Non-Directory File, Open No Recall Attributes: N ShareMode: Read AllocationSize: n/a Impersonating: DomainNameUserName
Attaching files that reside on a network-attached storage
If you cannot re-attach a database that resides on network-attached storage, a message like this may be logged in the Application log.
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "\servernamesharenamefilename.mdf". Operating system error 5: (Access is denied.).
This problem occurs because [!INCLUDEssNoVersion] resets the file permissions when the database is detached. When you try to reattach the database, a failure occurs because of limited share permissions.
To resolve, follow these steps:
-
Use the -T startup option to start [!INCLUDEssNoVersion]. Use this startup option to turn on trace flag 1802 in SQL Server Configuration Manager (see Trace Flags for information on 1802). For more information about how to change the startup parameters, see Database Engine Service Startup Options.
-
Use the following command to detach the database.
exec sp_detach_db DatabaseName go
-
Use the following command to reattach the database.
exec sp_attach_db DatabaseName, '\Network-attached storage_PathDatabaseMDFFile.mdf', '\Network-attached storage_PathDatabaseLDFFile.ldf' go
- Remove From My Forums
Unable to open physical file — Operating system error 5: 5(error not found) Microsoft SQL Server: Error 5120
-
Question
-
I am trying to attach a database to SQL 2005. This database has not previously been attached. I have only just installed SQL.
I get the following message: Unable to open physical file «C:ArrowSQLArr@DataArrow_data.mdf» Operating system error 5: «5(error not found)» (Microsoft SQL Server: Error 5120)».
I have loaded SQL and the database fiel and directory with the same user acccount which is a local adminstrator ont hsi machine. I have checked that I have read/write access to the file.
The machine runs Windows Vista Business. SQL has SP 2 loaded.
What causes this?
Answers
-
Chris,
Provide modify privilege for SQLService account for the folder C:ArrowSQLArr@Data and then attach the db, it will attach without privilege error!!
-
Use the below script to find the sql service account
Code Snippet
declare @sqlser varchar(20)
EXEC master..xp_regread @rootkey=‘HKEY_LOCAL_MACHINE’, @key=‘SYSTEMCurrentControlSetServicesMSSQLSERVER’,
@value_name=‘objectname’, @value=@sqlser OUTPUT
PRINT ‘Account Starting SQL Server Service:’ +convert(varchar(30),@sqlser)
After getting the service account try the below steps to provide privilege
-
Right click on the folder C:ArrowSQLArr@Data and click on properties
-
Click on security tab
-
Click on Add button and add sql service account
-
Provide modify privilege and click ok
-
Verify both mdf and ldf have modify privilege
-
Attach the db!
-
SQL (Structured Query Language) is a standardized programming language that is used for managing interpersonal databases and performing several operations on the data in them. MS SQL Server also works to store and retrieve data, as per demand by a user. However, while accessing the server, users encounter one or the other error, which interrupt ongoing processes of a machine.
SQL Server error 5120 is yet another error that is very common. Therefore, there is a need for having solutions by which one can instantly troubleshoot the Operating System error 5 SQL server error 5120.
This 5120 Error code ascends, when the version in which it is running on the SQL server service does not have the right to use MDF and LDF files of a database. Subsequently, in this post, we will discuss the solution to fix this error message.
Reasons for Operating System Error 5 SQL Server Error 5120 Occurrence
- The main reason is that MDF database files are not located at their accurate location. It means that an MDF file of the server might be store in some other drive of a computer.
- There is one more reason for such error occurrence i.e., system drives might not be having permissions to store the server file within them. Thus, when a user tries to connect database files to the server, it results in the occurrence of SQL server 5120 error message.
How to Fix Attach SQL Database Error 5120
Create two users account such as SQLAdminOne and SQLAdminTwo on the SQL server and then follow the steps shown below to solve the error 5120 in SQL server:
- Create A New Database: Follow the below-mentioned steps to generate a new database such as Test:
CREATE DATABASE [Test]
CONTAINMENT = NONE
ON PRIMARY
(NAME = N’Test’, FILENAME = N’D:SQLDATAMSSQL13.MSSQLSERVERMSSQLDATATest.mdf’)
LOG ON
(NAME = N’Test_log’, FILENAME = N’D:SQLDATAMSSQL13.MSSQLSERVERMSSQLDATATest_log.ldf’)
GO - Check Test File Permission For MDF and LDF: Browse the location of MDF and LDF files and then right-click on the file >> Properties >> Security
- SQLAdminOne Detaches Test Database: Right-clickon the Test database>> Tasks >> Detach
- Check Test File Permission for MDF and LDF Again: Test database MDF and LDF files permissions have changed and full permission are granted to SQLAdminOne only.
- SQLAdminTwo Tries To Attach Test Database: When SQLAdminTwo tries to attach the Test database SQL error code 5120 occurs due to the limited permission on the database files
- Grant Full Permission On both MDF and LDF Files of Test Database: Follow the below-mentioned steps to grant the full permission on the Test database file:
- Right-click on the Testdatabase file and then select Properties
- Now, click on Securitytab and then click on Edit button
- Click on the Addbutton and then enter the object name to select as NT ServiceMSSQL$SQL2013. After that, click on Check Names button and then click on OK
- Now, give this account Full Controlto the file and then click on OK button
- Reattach Test database: SQLAdmin Two become successful to attach the Test database.
What can you do when these steps does not work?
If these steps fail to fix your SQL Server Database Error 5120, it might be major problems within your SQL database. In condition, either restore entire data from SQL Backup or repair corrupted SQL Database using best SQL recovery software.
Conclusion
After reading this blog the reader and the user will get complete knowledge about how to fix the Operating System Error 5 SQL Server Error 5120. Generally this kind of error occurs either due to the absence of permissions or due to a wrong storage location of database log files. Therefore,in order to fix this issue users need to move database file (or MDF file) to its original location or will have to add legal permissions to a computer drive.
Related Post
Resolve Microsoft SQL Server Error 5120 Attach Database Problem
Microsoft SQL Server is a widely used database management system. The primary function of this application is to store and retrieve the data requested by the SQL user. And SQL (Structured Query language) is used to perform operations on the data. But sometimes SQL database users encounter one of the common SQL Server error 5120 attach database problems. Let us take an example of a Query asked by the user in one of the forum websites. Evidently, it will help us to understand the situation in a better way.
“Please help! While attaching the database with the MDF and LDF file. It shows an error like this Unable to open the physical file “D:mydatabase.mdf”. Operating system error 5: “5(Access is denied.)”. (Microsoft SQL Server, Error: 5120)”. Can anyone provide me the reasons for this error? Also, suggest the best possible way to resolve SQL Server 5120 error. Thanks!”
Basically, this problem occurs when Microsoft SQL Server database users don’t have the authorization to access the MDF and NDF files of the database. Before proceeding to the solution part let us first discuss the various causes of this problem.
Also Read: How to Recover Deleted Database in SQL Server Without Backup
Reasons Behind Microsoft SQL Server Error 5120 Attach Database Problem
1. If your MDF files are not at the accurate location or MDF database files are stored in some other drive of the computer.
2. In case if you are not running your Microsoft SQL Server management studio with admin rights.
3. If system drives don’t have permission to store the server file within them. Then also, in that case, it will show the error 5120 in SQL Server 2019, 2017, 2016, 2014, 2008 R2, 2008, and 2005.
Also Read: SQL Server Does Not Exist or Access Denied Error – Solved
Smart and Efficient Solution to This Problem – SQL Server Error 5120
If you are facing a Microsoft SQL server error 5120 attach database problem, I suggest you restore the entire data from the backup or repair the corrupt SQL database MDF file. If you want to recover the data without backup, you can take the help of SQL Server Recovery Tool. Also, it repairs corrupted MDF and NDF files and exports them to the SQL database, SQL Scripts, or CSV Format. The software allows the user to recover deleted objects such as Tables, stored procedures, functions, triggers, views, etc, Also, this software is compatible with SQL Server 2019 and its below version.
Download Now Purchase Now
Steps to Recover SQL Database without backup & Resolve Microsoft SQL Server Error 5120 :
Step-1. Install the Software on your machine. Click on Open and Browse the MDF file from your system.
Step-2. Now choose the Scan Mode, and select the SQL Server version, the user can select the recover deleted objects option to view deleted objects in red color.
Step-3. Click on the Database Objects to preview SQL database items.
Step-4. Click on the Export button to export the MDF file data to the SQL Server Database, SQL Script, or CSV File Format.
Conclusion
Finally, in this article, we have discussed the solution to SQL server error 5120 Attach Database error problem faced by the users. Also, we have given various reasons for this problem. Therefore, to resolve this issue the user can take the help of a professional tool. Hence, it is an advanced and standalone solution to resolve Microsoft SQL server error 5120 smartly.
Frequently Asked Questions
How to find the location of the MDF file in SQL Server 2016?
The default MDF file location for SQL Server 2016:
C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATATech.mdf – Knowing the default location, users can get an advantage in solving SQL server error 5120 safely.
Why I am not able to access or open MDF file?
If you cannot access the MDF file, your file may get damages. To fix this problem, you need to repair a corrupted MDF file and then easily access it in the SQL Server database.
Is this utility solve Microsoft SQL server error 5120 free?
No, This advanced tool is paid but yes, it offers a demo version that is completely free & users can test tool before buying. using this feature.