Microsoft sql server ошибка 5120

SQL is a domain specific language which is used for stream processing and in designing and programming for managing data in the relational data stream management system (RDSMS). MS SQL Server database provides the feasibility to access many records with a single command and also by using SQL database it is very easy for the users to migrate data from MS Access, Sybase, Oracle to MS SQL Server. Sometimes, while accessing the database, users encounter errors which hampers their way of doing work.

One such error is SQL Server Attach Database error 5120, it occurs due to when MS SQL Server database does not have the authorization to access LDF and MDF  files of a database.

Causes of Microsoft SQL Server Error 5120

• Not open and run SQL Server Management studio with admin right.
• When the MDF file is not in appropriate location.
• Maybe system server is not authorized to store the server files and when a user attempts to connect database files to server SQL server error occurs.
• Not using Windows Authentication account, while using SQL Server database because of which error 5120 occurs.

How to fix Microsoft SQL Server Attach Database error 5120 manually:

First, you need to create two user account like SQLAdminOne and SQLAdminTwo in order to fix error 5120  and then follow the below mentioned procedure:

• Create  a new database by following ways:

• Check Test File permission for MDF and LDF files:

Find and browse the location of MDF and LDF files & right click on the file >> Properties >> Security.

• SQLAdminOne Detaches Test Database: By right click on the Test database >> Tasks >> Detach
• Now, again check test file permission for MDF and LDF files: Test database LDF and MDF files permissions have changed and full permission are allowed to SQLAdminOne.

• SQLAdminTwo attempts to attach Test database: Whenever there is limited permission on the SQL server database files,  Microsoft SQL server error 5120 occurs.
• Allow full permission on MDF & LDF files of Test Database: To do this, you need to follow these step:

1. First, right-click on the Testdatabase file and select  Properties.
2. Click on Security tab & after that click on Edit.
3. Now, click on Add & enter the object name to select as NT ServiceMSSQL$SQL2013.
4. Immediately, after that click on Check Names & then click on OK.
5. Give this account Full Control to the file and click on OK.

6.  Reattach the Test database: Now, in this way, SQLAdminTwo is able to attach the Test database

Another Technique to resolve SQL Server Attach Database Error 5120:

If above-mentioned ways do not work and you are still facing error 5120, then in such case there are only two options left to do you can either restore all data from SQL Backup or fix it with the SQL Database Recovery software.

Salient Features of the software:

• Recover both database files MDF and NDF.
• Compatible with all versions of MS SQL Server.
• Supports Server ROW-compression as well as PAGE-compression.
• Save recovered data in new SQL database or in SQL script form.

Conclusion

After knowing the causes, the user can avoid Microsoft SQL server error 5120. In some cases, it can be solved by adding permissions to a drive or by allocating database files to its original location. And, if things don’t work by manual ways then a user can opt for an alternative technique.

Related Post

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

1. 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.
2. 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

Operating System Error 5 Access Denied Microsoft SQL Server Error 5120

MS SQL Server is a database management system whose function is to store and retrieve data, as per requested by a user. However, while accessing the server, users encounter one or the other error, which interrupt ongoing processes of a machine. Therefore, there is a need for having solutions by which one can instantly troubleshoot the Operating System error 5 SQL server error 5120.

One such server error occurs when users try to mount the database on the SQL server i.e., Attach database failed for server. In this post, we are going to discuss all possible reasons involved in its occurrence and solutions to deal with same.

Reasons of Operating System Error 5 SQL Server Error 5120 Occurrence

1. 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.

2. 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. Therefore, when a user tries to connect database files to the server, it results in the occurrence of SQL server 5120 error message.

Possible Solutions to Troubleshoot Problem

There are two possible ways by which one can fix this error and they are mention below:

• One way is to locate the directory and move MDF file (with its log file) to its original server path.

• Another way is to add the file security permission on the drive where the server file is stored.

1. Locate MDF File to the Server Directory:

   • Within your machine, search an MDF file, which you want to attach to the server

   • Now select the file having .mdf and .ldf file extension & then, copy or cut those files

     

   • Now minimize current screen of the machine and open Computer or My Computer. Double-click on C: drive for further proceedings

     

   • Click on Program Files from the C drive window and then double-click on Microsoft SQL Server

     

   • Double-click on MSSQL 12.MSSQLSERVER >> MSSQL >> DATA

     

   • Now, either press Ctrl+V or right-click on the current screen and select Paste option in order to paste the server files on its actual path

     

   • Now launch Microsoft SQL Server Management Studio and from the left-hand side of the window right-click on Database option

     

   • Click on Attach >> Add button for selecting the server file, which you want to attach

   • Navigate towards the location where the MDF file is store and select it. After selecting file, click on OK button

     

   • Again click on OK button from Attach Database window and then you will be able to access database file without encountering any server error

2. Assign Complete Security Permissions to A Drive

   With help of an Administrator Group, assign the file security permissions to the system drive with entire controls related to database files and log files. The source drive will be the one where required MDF file is currently locate. After enabling security permissions, you will easily be able to attach a database with the server.

Hit-And-Trial Workarounds to Fix Error

Following are some solutions by which one can fix Operating System Error 5 SQL Server Error 5120:

Note: A user can choose any one measure to troubleshoot the server error.

• On Win7 machine, create a directory with name ‘ProjectData’ and set default NTFS permissions for having full control. This will enable users to read the database, but not allow any modifications to it.

Note: Verify that domain account should not directly appear in the access list

• Copy the Adventureworks 2008LT MDF/LDF files to any other directory by using SQL Management studio for connecting a SQL server instance and then integrate security. Now take a trial to attach database

• Follow below-mentioned instructions to fix the problem:

1. Remove LDF file from the folder where machine saves both of the files

2. Launch SQL Management studio and try to attach database

3. In current window, you will encounter another list in which it is mention that ‘LDF file is not founded’

4. Now click on LDF file for selection and hit on Remove >> OK button

This workaround might work because a copy of the LDF file may be saved somewhere on your machine.

Conclusion

The Operating System Error 5 SQL Server Error 5120 generally occurs either due to the absence of permissions or due to a wrong storage location of database log files. Therefore, 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. In addition, it is recommended to the server users that they should assign permissions to all computer drives, before performing any other workaround to fix this error.

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.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;