Microsoft sql server ошибка 5120 отказано в доступе - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

Remove From My Forums

Присоединение базы данных

Вопрос
Вообщем, совершил глупость. Вместо отсоединения базы данных, удалил ее. Вернее, не ту удалил. Была копия базы, сделанная простым копированием. Так вот ее присоединить не удалось. Пишет «ошибка операционной системы 5:»(Отказано в доступе»). (Microsoft Sql
Server, ошибка 5120). У меня WINDOWS 7 Как выйти из ситуации?

Ответы

Попробуйте снять с файла все разрешения (ну или дать учетной записи, под которой запущен MS SQL разрешения на оба файла), также снимите все атрибуты, а вот потом попробуйте подключить.

Ну а если не поможет, то да, backup и restore.
- Помечено в качестве ответа
  
  12 июля 2012 г. 17:26

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

Источник

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

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

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

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

Источник

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.

Источник

<img itemprop="url image" decoding="async" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif?cver=0001" alt=" locked" /> Присоединение базы данных

Вопрос

Ответы

MSSQLSERVER_5120

Details

Explanation

User action

Access is denied

Attaching files that reside on a network-attached storage

What causes SQL error 5120?

How to fix SQL error 5120?

Add permission to the drive

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Reasons for Operating System Error 5 SQL Server Error 5120 Occurrence

How to Fix Attach SQL Database Error 5120

What can you do when these steps does not work?

Возможно, вам также будет интересно:

Присоединение базы данных