Sql операционная система вернула ошибку 5

I’m trying to execute the following script in SQL Server Management Studio:

USE [master]
GO

CREATE DATABASE [test1] ON PRIMARY (
  NAME = N'test1', 
  FILENAME =
    N'C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATAtest1.mdf',
  SIZE = 70656KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
LOG ON (
  NAME = N'test1_log',
  FILENAME =
    N'C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATAtest1_log.ldf',
  SIZE = 164672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

But I’m getting the error:

Msg 5123, Level 16, State 1, Line 2
CREATE FILE encountered operating system error 5 (Access is denied.)
while attempting to open or create the physical file
‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATAtest1.mdf’.

Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Already have all role permissions for my user, any ideas on what’s wrong?

Sometimes when you backup your SQL Server using SQLBackupAndFTP, SSMS or T-SQL Commands you can face the following issue Cannot Open Backup Device Operating System Error 5(Access is Denied). This has to be the most famous issue in taking SQL Server database backups. There are several reasons for this error and there are various ways to solve it. Some time ago we met this issue and for a time we were creating a backup and discovered a very helpful resolution to the issue. The issue was as described beneath:

Msg 3201, Level 16, State 1, Line 4
Cannot open backup device 'D:Adventureworks.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.

• That error might appear when you try to backup the SQL Server database to a network drive.
• Therefore, this problem message usually occurs as a result of mistaken permissions to the network drive.
• Also, this issue can occur when there’s no directory with Log or there is not enough space on your drive.

If you get that error using SQLBackupAndFTP try to specify a temporary folder for the backup job like C:Temp and check if it fixes the issue.

If it still throws the same error, or you get this issue using SQL Server Management Studio please check NTFS permissions for the folder you have specified and make sure if all users of your system have all permissions to it.

To avoid the issues related to NTFS permissions we recommend you don’t use a folder in your profile folder like C:Users….

Another but the more rare reason for which this issue may arise is that a backup file with the same name already exists in the folder and it’s Read-Only. To fix it just right-click on it then choose “Properties”, uncheck Read-Only, and press “Apply” to save all-new settings.

If “Read-Only” is unchecked but you still have this issue go to the “Security” and give all necessary permission to the user.

After all necessary configurations are done you will be able to perform SQL Server database backup successfully.

Several SQL database administrators have reported encountering SQL Server error 5123 with the message “operating system error ‘5 (Access is denied.)’”. The error occurs while trying to attach a database, which has been placed in different locations. The complete error message reads as:

“Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) CREATE FILE encountered operating system error 5 (Access is denied.) while attempting to open or create the physical file ‘filepath‘. (Microsoft SQL Server, Error: 5123)”

Possible Reasons behind Operating System Error 5 (access is denied) Error and Their Solutions

Following are the main reasons that may cause MS SQL ‘access denied error’ when attaching SQL database, along with their solutions:

Reason 1 – Default Location of SQL Data Files Has Changed

SQL database files (MDF and NDF) are moved from their default location on the system drive to another location.

Solution – Check the Default Location of the Data File

Go to the default location of the file folder where the SQL database (MDF/LDF) files are stored. Check whether the database files are present in their default location or not. If they are not present, paste them in the default folder.

Note: The database should not be in use when copying the files into the default folder.

The default location for SQL data files is as follows:

C:Program FilesMicrosoft SQL ServerMSSQL{nn}.MyInstance

Here, ‘nn’ stands for SQL Server version.

For example, following is the default location of DATA file of MS SQL version 14.

C:Program FilesMicrosoft SQL ServerMSSQL 14.SQLTECH2017MSSQLDATA

Reason 2 – SQL Server Resets File Permissions when Database is Detached

Although SQL Server administrator credentials might grant you the privilege to perform several activities, some activities require a different set of permissions.

For instance, your log-in credentials might allow you to remove the database but will throw the “SQL Server Access denied” error message when you attempt to re-attach it. The error occurs because the SQL Server resets the file permissions when the db is detached. And so, the database file permissions must be granted explicitly to reattach the database.

After the database has been attached, the permissions are reverted to the Database Engine SID NT SERVICEMSSQLSERVER account, and all privileges for individual log-in credentials are removed. While you can use the Database Engine SID NT SERVICEMSSQLSERVER account to attach database files, it might not always be easy to do so.

Solution – Grant Full Access Control on the SQL Data (MDF) and Log (LDF) Files

Let’s take an example to understand and fix the problem. In this example, two administrators, Adm1 and Adm2, have sysadmin (system administrator) rights on an SQL server instance. Here, we will use the first administrator’s credentials to remove a database, followed by using the second administrator’s credentials to attach the same database.

• Step 1 – Create the Example Database

For this example, an example database ‘db1’ is created on the system. The name of the database file is db1.mdf, and the name of the database log file is db1.ldf.

• Step 2 – Check the Permissions of the db1.mdf and db1.ldf Files

Open the db1 Properties window. Under the Security tab, select the server name, and grant all permissions of the database file to all users on the server instance.

• Step 3 – Remove the db1 Database File

Use the administrator credentials of Adm1 to detach the ‘db1’ database file from the server.

• Step 4 – Check the permissions of the db1.mdf and db1.ldf files again

When you check the permissions of both db1.mdf and db1.ldf, you will notice that, under Security, full permissions are applied only to Adm1.

• Step 5 – Attach the db1 database file

Use the administrator credentials of Adm2 to attach ‘db1’ back to the server. As seen in Step 4, all privileges now lie with only Adm1, which is why, when Adm2 tries to re-attach the db1 database file to the server, the system throws the SQL Error 5123: “SQL Server Access denied”.

• Step 6 – Apply Full Permissions to Both db1.mdf and db1.ldf Files

For ‘Adm2’  to be able to re-attach the db1 database file, full permissions for both the ‘db1.mdf’ and ‘db1.ldf’ files must be granted to Adm2.
Alternatively, full permissions to ‘db1.mdf’ and ‘db1.ldf’ files can be granted to the Database Engine SID NT SERVICEMSSQLSERVER account. Under the Security tab, select ‘Adm2’ and grant it full permissions to the db1.mdf and db1.ldf files.

• Step 7 – Re-attach the db1 Database File

Use ‘Adm2’ credentials to attach the db1 database file.

• Step 8 – Check the Permissions of the db1.mdf and db1.ldf Files One Last Time

After the ‘db1’ database file has been attached, the system removes full permissions for both ‘Adm1’ and ‘Adm2’ credentials. Full permissions are now granted only to the Database Engine SID NT SERVICEMSSQLSERVER account.

Also read: Potential causes of the “SQL Server does not exist or access denied” error message

What To Do If the Error Persists?

If the above methods fail to fix the issue, problems within your SQL database, such as corruption in the MDF file, may cause the ‘Access denied error’. In such a case, either perform a full restore from a recent database backup or repair the damaged SQL database using reliable SQL database repair software, such as Stellar Repair for MS SQL.

Key Features of Stellar Repair for MS SQL Software

1. It fixes all corruption of SQL Server database and recovers inaccessible objects from MDF and NDF file.
2. It carries out the highest level of non-destructive repair algorithm to preserve database integrity while recovering tables, triggers, indexes, keys, rules, and defaults.
3. Trusted by SQL Server MVP
4. Auto-detects the corrupt database version.
5. Show a preview of the recoverable sql database.
6. Saved the repaired database in 4 formats: MSSQL (.MDF), HTML. CSV and, XLS.
7. Supports MS SQL 2019, 2017, 2016 , and all lower versions.
8. Compatible with Windows 10 / 8 / 8.1 / 7 / Vista / XP.

Read this: How to Repair MDF File with Stellar Repair for MS SQL Software?

Conclusion

Permission denied was initiated as a security measure to prevent users from attaching files they didn’t create. Thus, use SQL log-in credentials to attach and remove database files only if you are sure which files you want to attach and what’s their source. The SQL log-in credentials use the Database Engine SID NT SERVICEMSSQLSERVER account, and this is a good way to eliminate the SQL database access denied error message as discussed in this blog. If you’re still unable to attach the database, likely, the MDF file is not in a healthy state (and is corrupted). Try using Stellar Repair for MS SQL to scan and repair MDF files and preserve their data integrity.

SQL 2005 Enterprise

Even if I logon as Administrator and try to backup any of my databases to local partitions, I get this error below. I have been operating fine up until this point and doing backups all the time.  As far as I know, and after checking, no permissions have changed and of course Administrator has ntfs permissions to my local C and other partitions on this server:

TITLE: Microsoft SQL Server Management Studio
——————————

Backup failed for Server ‘servername’.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Cannot open backup device ‘F:foldername’. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

——————————
BUTTONS:

OK
——————————

In the Event log for Windows Server 2003 I see this:

Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
Date:  3/6/2006
Time:  3:35:06 PM
User:  NT AUTHORITYNETWORK SERVICE
Computer: servername
Description:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
 to the user NT AUTHORITYNETWORK SERVICE SID (S-1-5-20).  This security permission can be modified using the Component Services administrative tool.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

   ac13

05.09.16 — 09:22

   Ёпрст

1 — 05.09.16 — 09:25

Отказано в доступе

   Ёпрст

2 — 05.09.16 — 09:26

Посмотри, кто владелец папки и какие на неё права

   ac13

3 — 05.09.16 — 09:28

(2) У всех полный доступ к папке

   Fedor-1971

4 — 05.09.16 — 09:38

(0) При запущенном сервере SQL базы не копируются. Останови службу сервера (и сервер 1С Предприятие?), потом пытайся копировать

(3) Путь и права на папку проверь именно у пользователя под которым стартован SQL, по умолчанию сервер стартует с локальным пользователем, а не доменным.

   ac13

5 — 05.09.16 — 09:41

   ac13

6 — 05.09.16 — 09:46

(4) хотя при копировании да, вы правы. Но вот резервное копирование всё-равно выполняться не хочет и при выключенном SQL сервере скопировать базу не дает.

   Fedor-1971

7 — 05.09.16 — 10:15

(6) истёк срок действия пароля пользователя? выкинули из группы локальных админов?

Просто перезагрузи сервер, сразу выяснится вся информация о пользователе SQL (или всё станет на свои места, если был обрыв связи с доменом).

   ac13

8 — 05.09.16 — 10:22

(7) нет, пароль неограничен, из локальной группы не выкидывали и сервер уже перезагружал)

   ac13

9 — 05.09.16 — 10:28

(7) а это может быть связано с появлением еще одного сервера в сети одной рабочей группы? на нем тоже стоит SQL и пользователь Администратор, только с другим паролем. или это тут вообще не при чем?

   Fedor-1971

10 — 05.09.16 — 10:30

(9) по идее не должно влиять, но, на всякий случай, раздели имена пользователей, хотя-бы Админ1 и Админ2

   Новиков

11 — 05.09.16 — 10:48

Засунь этого пользователя, под которым стартует скл, в группу локальных администраторов и этой группе напрямую дай права в свою папку. Получится ль?

   ac13

12 — 05.09.16 — 10:55

(11) Пользователь, под которым стартует SQL — Администратор — Встроенная учетная запись сервера.

   Новиков

13 — 05.09.16 — 10:56

А ты залогинься под ним, и посмотри — можешь в эту папку писать хоть что-то или нет?

   ac13

14 — 05.09.16 — 10:57

(13) под ним и залогинен. да могу.

   Новиков

15 — 05.09.16 — 11:01

хм. а попробуй новую базу сделать, просто пустую, и сделать ее резервную копию. Будет ли работать такое?

   ac13

16 — 05.09.16 — 11:40

(15) нет, с новой не получилось. на любую базу так выдает. думаю, может с СКЛ-сервером что-то

   Новиков

17 — 05.09.16 — 11:51

Сделай нового пользователя, локального администратора, запусти скл-сервер под ним, и попробуй тоже самое.

   ac13

18 — 05.09.16 — 12:27

(17) увы нет, не сработало. Ладно, буду думать и пытаться. Спасибо!!!

   Ёпрст

19 — 05.09.16 — 12:33

Рестарт самого скуля хоть делал ?

   ac13

20 — 05.09.16 — 12:54

(19) конечно)

   leonidkorolev

21 — 05.09.16 — 13:13

Посмотри под кем работает служба скуля. Дай этому пользователю доступ на папку или включи его в группу админов.

   ac13

22 — 05.09.16 — 14:11

(21) служба работает от MSSQLSERVER, но такого пользователя нет в списке пользователей сервера

   leonidkorolev

23 — 05.09.16 — 14:21

(22) Надо ручками писать это имя

   leonidkorolev

24 — 05.09.16 — 14:42

   ac13

25 — 05.09.16 — 14:51

(24) спасибо! это нужно прописать в доступе к папке с базами SQL? прописал, ничего не вышло. и к серверу SQL соединяюсь под пользователем Администратор, когда пытаюсь резервную копию сделать

   leonidkorolev

26 — 05.09.16 — 14:56

(25) Прописать доступ к папкам с бэкапами

(26) вы представляете! получилось! спасибо большое!

I want to restore a database from a file (Tasks → Restore → Database; after I select from device and select file) via SQL Server Management Studio.

After that, I get this error:

The operating system returned the error ‘5(Access is denied.)’ while attempting
‘RestoreContainer::ValidateTargetForCreation’ on ‘E:Program FilesMicrosoft SQL
ServerMSSQL10.MSSQLSERVERMSSQLDATAXXXXXX.mdf’.
Msg 3156, Level 16, State 8, Server XXXX, Line 2

How do I fix this problem? Is it a security error?

Jyoti Prakash

Problem solver and Data recovery specialist. Usually share informative articles on data recovery, database corruption and ways to recover lost data.

Источник

SQL 2005 Enterprise

Even if I logon as Administrator and try to backup any of my databases to local partitions, I get this error below. I have been operating fine up until this point and doing backups all the time.  As far as I know, and after checking, no permissions have changed and of course Administrator has ntfs permissions to my local C and other partitions on this server:

TITLE: Microsoft SQL Server Management Studio
——————————

Backup failed for Server ‘servername’.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Cannot open backup device ‘F:foldername’. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

——————————
BUTTONS:

OK
——————————

In the Event log for Windows Server 2003 I see this:

Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
Date:  3/6/2006
Time:  3:35:06 PM
User:  NT AUTHORITYNETWORK SERVICE
Computer: servername
Description:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
 to the user NT AUTHORITYNETWORK SERVICE SID (S-1-5-20).  This security permission can be modified using the Component Services administrative tool.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Источник

   ac13

05.09.16 — 09:22

Здравствуйте!

Стоит MS SQL Server 2012. Всё работало нормально, но в один день пропала возможность копировать базу и создавать резервные копии. При попытке создать резервную копию базы выдает ошибку:

Действие резервное копирование завершилось неудачно для объекта «Сервер» «SERVER».

(Microsoft.SqlServer.SmoExtended)

Дополнительные сведения:

System.Data.SqlClient.SqlError: Невозможно открыть устройство резервного копирования

‘D:Backup SQLtradedb_backup.bak’. Ошибка операционной системы 5 (Отказано в доступе.).

(Microsoft.SqlServer.Smo)

Если попытаться сделать резервную копию в другую папку — то же самое. И так же не дает просто скопировать файлы базы данных из папки, где хранятся базы SQL. Подскажите, с чем может быть связана эта проблема и как её решить?

   Ёпрст

1 — 05.09.16 — 09:25

Отказано в доступе

   Ёпрст

2 — 05.09.16 — 09:26

Посмотри, кто владелец папки и какие на неё права

   ac13

3 — 05.09.16 — 09:28

(2) У всех полный доступ к папке

   Fedor-1971

4 — 05.09.16 — 09:38

(0) При запущенном сервере SQL базы не копируются. Останови службу сервера (и сервер 1С Предприятие?), потом пытайся копировать

(3) Путь и права на папку проверь именно у пользователя под которым стартован SQL, по умолчанию сервер стартует с локальным пользователем, а не доменным.

   ac13

5 — 05.09.16 — 09:41

(4) спасибо. Дело в том, что раньше всё копировалось и при запущенном SQL-сервере в том числе.

Права ни на папки, ни у пользователей никто не менял. Это просто произошло в один день

Хорошо, я попробую отключить службу SQL сервера и скопировать.

   ac13

6 — 05.09.16 — 09:46

(4) хотя при копировании да, вы правы. Но вот резервное копирование всё-равно выполняться не хочет и при выключенном SQL сервере скопировать базу не дает.

   Fedor-1971

7 — 05.09.16 — 10:15

(6) истёк срок действия пароля пользователя? выкинули из группы локальных админов?

Просто перезагрузи сервер, сразу выяснится вся информация о пользователе SQL (или всё станет на свои места, если был обрыв связи с доменом).

   ac13

8 — 05.09.16 — 10:22

(7) нет, пароль неограничен, из локальной группы не выкидывали и сервер уже перезагружал)

   ac13

9 — 05.09.16 — 10:28

(7) а это может быть связано с появлением еще одного сервера в сети одной рабочей группы? на нем тоже стоит SQL и пользователь Администратор, только с другим паролем. или это тут вообще не при чем?

   Fedor-1971

10 — 05.09.16 — 10:30

(9) по идее не должно влиять, но, на всякий случай, раздели имена пользователей, хотя-бы Админ1 и Админ2

   Новиков

11 — 05.09.16 — 10:48

Засунь этого пользователя, под которым стартует скл, в группу локальных администраторов и этой группе напрямую дай права в свою папку. Получится ль?

   ac13

12 — 05.09.16 — 10:55

(11) Пользователь, под которым стартует SQL — Администратор — Встроенная учетная запись сервера.

   Новиков

13 — 05.09.16 — 10:56

А ты залогинься под ним, и посмотри — можешь в эту папку писать хоть что-то или нет?

   ac13

14 — 05.09.16 — 10:57

(13) под ним и залогинен. да могу.

   Новиков

15 — 05.09.16 — 11:01

хм. а попробуй новую базу сделать, просто пустую, и сделать ее резервную копию. Будет ли работать такое?

   ac13

16 — 05.09.16 — 11:40

(15) нет, с новой не получилось. на любую базу так выдает. думаю, может с СКЛ-сервером что-то

   Новиков

17 — 05.09.16 — 11:51

Сделай нового пользователя, локального администратора, запусти скл-сервер под ним, и попробуй тоже самое.

   ac13

18 — 05.09.16 — 12:27

(17) увы нет, не сработало. Ладно, буду думать и пытаться. Спасибо!!!

   Ёпрст

19 — 05.09.16 — 12:33

Рестарт самого скуля хоть делал ?

   ac13

20 — 05.09.16 — 12:54

(19) конечно)

   leonidkorolev

21 — 05.09.16 — 13:13

Посмотри под кем работает служба скуля. Дай этому пользователю доступ на папку или включи его в группу админов.

   ac13

22 — 05.09.16 — 14:11

(21) служба работает от MSSQLSERVER, но такого пользователя нет в списке пользователей сервера

   leonidkorolev

23 — 05.09.16 — 14:21

(22) Надо ручками писать это имя

   leonidkorolev

24 — 05.09.16 — 14:42

   ac13

25 — 05.09.16 — 14:51

(24) спасибо! это нужно прописать в доступе к папке с базами SQL? прописал, ничего не вышло. и к серверу SQL соединяюсь под пользователем Администратор, когда пытаюсь резервную копию сделать

   leonidkorolev

26 — 05.09.16 — 14:56

(25) Прописать доступ к папкам с бэкапами

  

ac13

27 — 05.09.16 — 15:06

(26) вы представляете! получилось! спасибо большое!

Источник

I want to restore a database from a file (Tasks → Restore → Database; after I select from device and select file) via SQL Server Management Studio.

After that, I get this error:

The operating system returned the error ‘5(Access is denied.)’ while attempting
‘RestoreContainer::ValidateTargetForCreation’ on ‘E:Program FilesMicrosoft SQL
ServerMSSQL10.MSSQLSERVERMSSQLDATAXXXXXX.mdf’.
Msg 3156, Level 16, State 8, Server XXXX, Line 2

How do I fix this problem? Is it a security error?

Uwe Keim

39.1k56 gold badges176 silver badges290 bronze badges

asked Aug 16, 2010 at 15:03

1

I recently had this problem. The fix for me was to go to the Files page of the Restore Database dialog and check «Relocate all files to folder».

Uwe Keim

39.1k56 gold badges176 silver badges290 bronze badges

answered Jun 21, 2013 at 11:13

Jamie HumphriesJamie Humphries

3,2682 gold badges18 silver badges21 bronze badges

4

The account that sql server is running under does not have access to the location where you have the backup file or are trying to restore the database to. You can use SQL Server Configuration Manager to find which account is used to run the SQL Server instance, and then make sure that account has full control over the .BAK file and the folder where the MDF will be restored to.

Greg Bray

14.7k11 gold badges81 silver badges104 bronze badges

answered Aug 16, 2010 at 15:07

SQLMenaceSQLMenace

131k25 gold badges203 silver badges225 bronze badges

2

Well, In my case the solution was quite simple and straight.

I had to change just the value of log On As value.

Steps to Resolve-

1. Open Sql Server Configuration manager
2. Right click on SQL Server (MSSQLSERVER)
3. Go to Properties

1. change log On As value to LocalSystem

Hoping this will help you too

answered Apr 25, 2017 at 15:07

Vikash PandeyVikash Pandey

5,3816 gold badges40 silver badges42 bronze badges

1

I just ran into this same problem but had a different fix. Essentially I had both SQL Server and SQL Server Express installed on my computer. This wouldn’t work when I attempted to restore to SQL Express, but worked correctly when I restored it to SQL Server.

answered Mar 16, 2012 at 13:34

1

A good solution that can work is go to files > and check the reallocate all files

DaFois

2,1878 gold badges26 silver badges43 bronze badges

answered Oct 7, 2019 at 13:37

I tried the above scenario and got the same error 5 (access denied). I did a deep dive and found that the file .bak should have access to the SQL service account. If you are not sure, type services.msc in Start -> Run then check for SQL Service logon account.

Then go to the file, right-click and select Security tab in Properties, then edit to add the new user.

Finally then give full permission to it in order to give full access.

Then from SSMS try to restore the backup.

Nathan Tuggy

2,23427 gold badges30 silver badges38 bronze badges

answered Feb 10, 2015 at 0:58

2

I was getting the same error while trying to restore SQL 2008 R2 backup db in SQL 2012 DB. I guess the error is due to insufficient permissions to place .mdf and .ldf files in C drive. I tried one simple thing then I succeeded in restoring it successfully.

Try this:

In the Restore DB wizard windows, go to Files tab, change the restore destination from C: to some other drive. Then proceed with the regular restore process. It will definitely get restores successfully!

Hope this helps you too. Cheers

answered Mar 10, 2016 at 8:13

There are several causes for this error, I got this error because I checked «Reallocate all files to folder» in the Files tab of Restore Database window but the default path did not exist on my local machine. I had the ldf/mdf files in another folder, once I changed that I was able to restore.

answered Nov 28, 2017 at 0:05

cheriejwcheriejw

3543 silver badges13 bronze badges

1

The operating system returned the error ‘5(access denied.)’ when restoring database in sql server can be solved by enabling the Relocate all files to folder in the Files options as follows:

answered Apr 5, 2022 at 0:58

P.GithinjiP.Githinji

1,39910 silver badges5 bronze badges

I found this, and it worked for me:

CREATE LOGIN BackupRestoreAdmin WITH PASSWORD='$tr0ngP@$$w0rd'
GO
CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin
GO
EXEC sp_addsrvrolemember 'BackupRestoreAdmin', 'dbcreator'
GO
EXEC sp_addrolemember 'db_owner','BackupRestoreAdmin'
GO

answered May 24, 2012 at 22:50

Tom StickelTom Stickel

19.4k6 gold badges110 silver badges113 bronze badges

2

In my case I had to check the box in Overwrite the existing database (WITH REPLACE) under Options tab on Restore Database page.

The reason I was getting this error: because there was already an MDF file present for the database and it was not getting overwritten.

Hope this will help someone.

answered Jan 27, 2014 at 15:07

NewbeeNewbee

1,3792 gold badges16 silver badges36 bronze badges

If you’re attaching a database, take a look at the «Databases to attach» grid, and specifically in the Owner column after you’ve specified your .mdf file. Note the account and give Full Permissions to it for both mdf and ldf files.

answered Oct 29, 2014 at 19:53

jgojgo

3722 silver badges12 bronze badges

I had exactly same problem but my fix was different — my company is encrypting all the files on my machines. After decrypting the file MSSQL did not have any issues to accessing and created the DB. Just right click .bak file -> Properties -> Advanced… -> Encrypt contents to secure data.

answered Sep 8, 2017 at 13:50

1

this happened to me earlier today, i was a member of the local server’s admin group and have unimpeded access, or i thought so. I also ticked the «replace» option, even though there is no such DB in the instance.

Found out that there used to be DB of the same name there, and the MDF and LDF files are still physically located at the data and log folders of the server, but the actual metadata is missing in the sys.databases. the service account of SQL server also can’t ovewrwrite the existing files. Found out also that the files’ owner is «unknown», i had to change ownership, to the 2 files above so that it is now owned by the local server’s admin group, then renamed it.

Then finally, it worked.

answered Oct 7, 2017 at 6:39

user1465073user1465073

3158 silver badges21 bronze badges

The account does not have access to the location for backup file.
Take the following steps to access the SQL Server Configuration Manager via Computer Manager easily

1. Click the Windows key + R to open the Run window.
2. Type compmgmt.msc in the Open: box.
3. Click OK.
4. Expand Services and Applications.
5. Expand SQL Server Configuration Manager.
6. Change User Account in Log On As tab .

Now you can Restore Data Base easily

answered Jul 13, 2019 at 19:15

reza.bmreza.bm

1711 gold badge3 silver badges8 bronze badges

The fix for me was to go into Options when trying to Restore the database and change the path to the new path.
Here is the screenshot

answered Dec 17, 2019 at 20:37

I encountered the same problem, but my setup is a bit different.

• I run my database in a linux docker container
• sqlserver management tool in Windows.

What I did was:

sudo docker exec -u root -it sqlserver /bin/bash

This enters the docker container as a root user.

Then:

chmod 777 /path/to/file.bak

777 gives read, write & execute permissions to the file for any group, user

answered Sep 19, 2022 at 16:26

NielsNiels

1861 silver badge10 bronze badges

I am starting to learn SQL and I have a book that provides a database to work on. These files below are in the directory but the problem is that when I run the query, it gives me this error:

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file
«C:MurachSQL Server 2008DatabasesAP.mdf». Operating system error
5: «5(Access is denied.)».

   CREATE DATABASE AP
      ON PRIMARY (FILENAME = 'C:MurachSQL Server 2008DatabasesAP.mdf')
      LOG ON (FILENAME =     'C:MurachSQL Server 2008DatabasesAP_log.ldf')
      FOR ATTACH
    GO

In the book the author says it should work, but it is not working in my case. I searched but I do not know exactly what the problem is, so I posted this question.

MAbraham1

1,7074 gold badges28 silver badges45 bronze badges

asked Aug 17, 2013 at 8:30

1

SQL Server database engine service account must have permissions to read/write in the new folder.

Check out this

To fix, I did the following:

Added the Administrators Group to the file security permissions with
full control for the Data file (S:) and the Log File (T:).

Attached the database and it works fine.

answered Aug 17, 2013 at 8:37

Rahul TripathiRahul Tripathi

167k31 gold badges276 silver badges330 bronze badges

12

An old post, but here is a step by step that worked for SQL Server 2014 running under windows 7:

• Control Panel ->
• System and Security ->
• Administrative Tools ->
• Services ->
• Double Click SQL Server (SQLEXPRESS) -> right click, Properties
• Select Log On Tab
• Select «Local System Account» (the default was some obtuse Windows System account)
• -> OK
• right click, Stop
• right click, Start

Voilá !

I think setting the logon account may have been an option in the installation, but if so it was not the default, and was easy to miss if you were not already aware of this issue.

answered Feb 17, 2016 at 18:38

10

To get around the access denied issue, I started SSMS as administrator and that allowed me to attach a database from my local drive. The database was created in another SQL and windows instance.

answered Feb 23, 2015 at 11:13

JayKayOf4JayKayOf4

1,19211 silver badges15 bronze badges

4

This is Windows related issue where SQL Server does not have the appropriate permission to the folder that contains .bak file and hence this error.

The easiest work around is to copy your .bak file to default SQL backup location which has all the necessary permissions. You do not need to fiddle with anything else. In SQL SERVER 2012, this location is

D:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackup (SQL 2012)
C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBackup (SQL 2014)
C:Program FilesMicrosoft SQL ServerMSSQL13.SQLEXPRESSMSSQLBackup (SQL 2016)

answered Oct 9, 2014 at 8:25

TheTechGuyTheTechGuy

16.2k16 gold badges112 silver badges135 bronze badges

6

I had this problem. Just run SQL Server as administrator

Adrian Mole

49.1k147 gold badges50 silver badges78 bronze badges

answered Jul 5, 2017 at 0:55

3

Yes,It’s right.The first you should find out your service account of sqlserver,you can see it in Task Manager when you press ctrl+alt+delete at the same time;Then,you must give the read/write privilege of «C:MurachSQL Server 2008Databases» to the service account.

answered Aug 17, 2013 at 8:48

TimTim

1491 silver badge9 bronze badges

0

I solve this problem by adding Full control permission for both .mdf and .ldf files for Users group.

answered Dec 2, 2016 at 15:26

1

The problem is due to lack of permissions for SQL Server to access the mdf & ldf files. All these procedures will work :

1. you can directly change the MSSQLSERVER service startup user account, with the user account who have better privileges on the files. Then try to attach the database.
2. Or you can assign the user to the file in security tab of the mdf & ldf files properties with read and and write privileges checked.
3. Startup with windows administrator account, and open SQL Server with run as administrator option and try to login with windows authentication and now try to attach the database.

Manu

4,3586 gold badges42 silver badges77 bronze badges

answered Mar 13, 2015 at 12:12

For me it was solved in the following way with SQL Server Management studio
-Log in as admin (I logged in as windows authentication)
-Attach the mdf file (right click Database | attach | Add )
-Log out as admin
-Log in as normal user

answered May 23, 2016 at 13:03

0

The actual server permissions will not matter at this point; all looks ok.
SQL Server itself needs folder permissions.
depending on your version, you can add SERVERNAME$MSSQLSERVER permissions to touch your folder. Othewise, it has to be in the default BACKUP directory (either where you installed it or default to c:programfiles(x)MSSQLBACKUP.

answered Sep 11, 2014 at 19:27

ClaudiaClaudia

511 silver badge1 bronze badge

Even if you do the following steps you COULD get the same error message.

1. login as SA user (SSMS)
2. Edit the file permissions to say "everyone" full access (windows folder)
3. Delete the Log file (Windows Exploring  (this was what I had done per advise from some msdn forum) 

I still GOT the permission error, but then I noticed that in the Attach screen, the bottom section STILL showed the LOG file, and the error message remained the same.

Hope this helps someone who did the same thing.

answered May 21, 2014 at 0:18

Tom StickelTom Stickel

19.4k6 gold badges110 silver badges113 bronze badges

It means the SSMS login user does not have permission on the .mdf file. This is how it has worked for me:

I had opened the SSMS (Run as administrator) and login as an administrator user, database right-click attach, click add, select the .mdf file, click Ok. Done.

answered Jul 15, 2020 at 22:44

GoldfishGoldfish

5645 silver badges11 bronze badges

I had this issue when I try to backup a database.

System.Data.SqlClient.SqlError: 
Cannot open backup device 'C:xxxxx.bak'. 
Operating system error 5 (Access is denied.). (Microsoft.SqlServer.Smo)

When I had this issue I thought that the user which I’m connecting to database don’t have rights to access to the backup location. I gave full control to that user but nothing changed. This is because the service for SQL Server is running with another user.

At this point you may choose changing user of the service to local system account or add access rights for the current user of the service.

I choose the 2nd one.

After that change backup succeeded.

answered May 20, 2022 at 9:47

Ozan BAYRAMOzan BAYRAM

2,6541 gold badge27 silver badges34 bronze badges

1

Very Simple Solution.

1. Login with System admin
2. copy your mdf and ldf files in «C:Program Files (x86)Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA» Where all other data file recides.
3. Now attach from there it will work

Draken

3,16913 gold badges35 silver badges53 bronze badges

answered Sep 20, 2016 at 10:19

1

I used Entity framework in my application and had this problem,I seted any permission in folders and windows services and not work,
after that I start my application as administrator (right click in exe file and select «run as admin») and that works fine.

answered Jul 17, 2017 at 7:45

Ali YousefiAli Yousefi

2,3552 gold badges32 silver badges46 bronze badges

If you get this error on an .MDF file in the APP_DATA folder (or where ever you put it) for a Visual Studio project, the way I did it was to simply copy permissions from the existing DATA folder here (I’m using SQL Express 2014 to support an older app):

C:Program FilesMicrosoft SQL ServerMSSQL12.SQLEXPRESS2014MSSQLDATA

(note: your actual install path my vary — especially if your instance name is different)

Double click on theDATA folder first as administrator to make sure you have access, then open the properties on the folder and mimic the same for the APP_DATA folder. In my case the missing user was MSSQL$SQLEXPRESS2014 (because I named the instance SQLEXPRESS2014 — yours may be different). That also happens to be the SQL Server service username.

answered Jan 24, 2019 at 1:37

James WilkinsJames Wilkins

6,6982 gold badges47 silver badges73 bronze badges

For some reason, setting all the correct permissions did not help in my case. I had a file db.bak that I was not able to restore due to the 5(Access is denied.) error. The file was placed in the same folder as several other backup files and all the permissions were identical to other files. I was able to restore all the other files except this db.bak file. I even tried to change the SQL Server service log on user — still the same result. I’ve tried copying the file with no effect.

Then I attempted to just create an identical file by executing

type db.bak > db2.bak

instead of copying the file. And voila it worked! db2.bak restored successfully.

I suspect that some other problems with reading the backup file may be erroniously reported as 5(Access is denied.) by MS SQL.

answered Nov 27, 2019 at 8:49

ps_ttfps_ttf

1,0967 silver badges15 bronze badges

In linux, I went to /var/opt/mssql/data/ folder and opened a terminal with sudo then, changed my *.mdf and *.ldf file permissions as below in which you replace yourDB with your Database file name and myUser to currently logged username:

chmod 755 yourDB.mdf
chown myUser yourDB.mdf

chmod 755 yourDB.ldf
chown myUser yourDB.ldf

After that, it was reconnected without any issue.

answered Jan 8, 2020 at 8:07

If the database you are trying to attach is compressed it may show error message.

First you have to decompress the file. For that go to properties of mdf/ldf file >> then «Advanced» >> Uncheck «Compress Contents to save disk space» >> Press «Apply».

After that give it a try.

Dharman♦

29.9k22 gold badges82 silver badges132 bronze badges

answered May 20, 2021 at 19:47

1

1. Run SQL Server management studio as Administrator
2. Log in as Windows user
3. Remove log file if you have only MDF file (haven’t log file)

With 3 items in the above checklist, you will remove almost problems related with attach database task.

answered May 22, 2021 at 5:17

If you’re using Storage Gateway — SMB (S3)
Do this from the management studio

1. EXEC xp_cmdshell ‘net use X: 100.155.16.6mystoragegatewayfolder xxmysuperpassxx /user:sgw-445577smbguest /persistent:yes /y’

2. EXEC XP_CMDSHELL ‘Dir X:’ (this should show you the Directory info, serial no etc)

3. Mount the drive (This PC — > Mount network drive, using the same info above)
   Test run a backup job using scheduled backup, this will force to use the sql server agent and you can see where it’s writing and whats the issue if any.

answered Aug 9, 2022 at 4:51

I’m trying to execute the following script in SQL Server Management Studio:

USE [master]
GO

CREATE DATABASE [test1] ON PRIMARY (
  NAME = N'test1', 
  FILENAME =
    N'C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATAtest1.mdf',
  SIZE = 70656KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
LOG ON (
  NAME = N'test1_log',
  FILENAME =
    N'C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATAtest1_log.ldf',
  SIZE = 164672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

But I’m getting the error:

Msg 5123, Level 16, State 1, Line 2
CREATE FILE encountered operating system error 5 (Access is denied.)
while attempting to open or create the physical file
‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATAtest1.mdf’.

Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Already have all role permissions for my user, any ideas on what’s wrong?

Я хочу восстановить базу данных из файла (задачи->восстановить->база данных; после выбора из устройства и выберите файл) через SQL server management studio.

после этого, я получаю эту ошибку:

операционная система вернула ошибку ‘ 5(Доступ запрещен.) при попытке
‘RestoreContainer:: ValidateTargetForCreation’ on ‘E:Program файлыMicrosoft SQL
Иметь значение mssql10 сервер.ИМЯ MSSQLSERVERMSSQL ДАННЫХДАННЫЕХХХХХХ.МДФ».
Msg 3156, Уровень 16, Состояние 8, сервер XXXX, Строка 2

Как исправить эту проблему? Это ошибка безопасности?

12 ответов

---

учетная запись, под которой работает sql server, не имеет доступа к местоположению, в котором находится файл резервной копии, или пытается восстановить базу данных. Вы можете использовать диспетчер конфигурации SQL Server найти, какая учетная запись используется для запуска экземпляра SQL Server, а затем убедитесь, что учетная запись имеет полный контроль над .Файл BAK и папка, в которую будет восстановлен MDF.

---

У меня недавно была эта проблема. Исправление для меня состояло в том, чтобы перейти на страницу файлов диалогового окна восстановления базы данных и проверить «переместить все файлы в папку».

134

автор: Jamie Humphries

---

Ну, в моем случае решение было довольно простым и прямым.

мне пришлось изменить только значение log On As значение.

действия разрешения-

1. открыть Sql Server Configuration manager
2. право click on SQL Server (MSSQLSERVER)
3. на Properties

1. изменить log On As значение LocalSystem

надеюсь, что это поможет вам тоже:)

---

Я просто столкнулся с этой же проблемой, но другим исправить. По сути, на моем компьютере были установлены SQL Server и SQL Server Express. Это не сработает, когда я попытаюсь восстановить SQL Express, но работал правильно, когда я восстановил его на SQL Server.

---

Я нашел это, и это сработало для меня:

CREATE LOGIN BackupRestoreAdmin WITH PASSWORD='$tr0ngP@$$w0rd'
GO
CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin
GO
EXEC sp_addsrvrolemember 'BackupRestoreAdmin', 'dbcreator'
GO
EXEC sp_addrolemember 'db_owner','BackupRestoreAdmin'
GO

---

Если вы присоединяете базу данных, посмотрите на сетку «базы данных для присоединения» и, в частности, в столбце владелец после того, как вы указали свой .файл mdf. Обратите внимание на учетную запись и дайте ей полные разрешения для файлов mdf и ldf.

---

Я попробовал приведенный выше сценарий и получил ту же ошибку 5 (доступ запрещен). Я глубоко нырнул и обнаружил, что файл .бак должен иметь доступ к учетной записи службы SQL. Если вы не уверены, введите services.msc в меню Пуск — > Выполнить, затем проверьте учетную запись входа в службу SQL.

затем перейдите к файлу, щелкните правой кнопкой мыши и выберите вкладку Безопасность в свойствах, затем отредактируйте, чтобы добавить нового пользователя.

наконец, затем дать полное разрешение на него, чтобы дать полный доступ.

затем из SSMS попробуйте восстановить резервную копию.

---

Я получал ту же ошибку при попытке восстановить SQL 2008 R2 backup db в SQL 2012 DB. Я предполагаю, что ошибка связана с недостаточными разрешениями для размещения .MDF и. файлы ldf на диске C. Я попробовал одну простую вещь, затем мне удалось успешно восстановить ее.

попробуйте это:

в окне мастера восстановления БД перейдите на вкладку файлы, измените назначение восстановления С C: на другой диск. Затем продолжайте обычный процесс восстановления. Он обязательно получит восстанавливается успешно!

надеюсь, это вам тоже поможет. Ура

---

в моем случае мне пришлось поставить галочку в Overwrite the existing database (WITH REPLACE) под Options tab on Restore Database страница.

надеюсь, это поможет кому-то.

---

У меня была точно такая же проблема, но мое исправление было другим — моя компания шифрует все файлы на моих машинах. После расшифровки файла MSSQL не возникло никаких проблем с доступом и создана БД. Просто щелкните правой кнопкой мыши .файл bak ->свойства ->дополнительно… ->шифровать содержимое для защиты данных.

0

автор: Radoslaw Jurewicz

---

Это случилось со мной ранее сегодня, я был членом группы администратора локального сервера и имел беспрепятственный доступ, или я так думал. Я также отметил опцию «заменить», хотя в экземпляре нет такой БД.

выяснил, что раньше там была БД с тем же именем, а файлы MDF и LDF по-прежнему физически расположены в папках данных и журналов сервера, но фактические метаданные отсутствуют в sys.база данных. учетная запись службы SQL server также не может ovewrwrite существующие файлы. Узнал также, что владелец файлов «неизвестен», мне пришлось сменить владельца на 2 файла выше, чтобы теперь он принадлежал группе администраторов локального сервера, а затем переименовал его.

затем, наконец, это сработало.

---

Я получил эту ошибку, потому что я проверил «перераспределить все файлы в папку» на вкладке «файлы» окна «восстановить базу данных», но путь по умолчанию не существовал на моей локальной машине. У меня были файлы ldf/mdf в другой папке, как только я изменил, что я смог восстановить.

---

Sometimes when you backup your SQL Server using SQLBackupAndFTP, SSMS or T-SQL Commands you can face the following issue Cannot Open Backup Device Operating System Error 5(Access is Denied). This has to be the most famous issue in taking SQL Server database backups. There are several reasons for this error and there are various ways to solve it. Some time ago we met this issue and for a time we were creating a backup and discovered a very helpful resolution to the issue. The issue was as described beneath:

Msg 3201, Level 16, State 1, Line 4
Cannot open backup device 'D:Adventureworks.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.

• That error might appear when you try to backup the SQL Server database to a network drive.
• Therefore, this problem message usually occurs as a result of mistaken permissions to the network drive.
• Also, this issue can occur when there’s no directory with Log or there is not enough space on your drive.

If you get that error using SQLBackupAndFTP try to specify a temporary folder for the backup job like C:Temp and check if it fixes the issue.

If it still throws the same error, or you get this issue using SQL Server Management Studio please check NTFS permissions for the folder you have specified and make sure if all users of your system have all permissions to it.

To avoid the issues related to NTFS permissions we recommend you don’t use a folder in your profile folder like C:Users….

Another but the more rare reason for which this issue may arise is that a backup file with the same name already exists in the folder and it’s Read-Only. To fix it just right-click on it then choose “Properties”, uncheck Read-Only, and press “Apply” to save all-new settings.

If “Read-Only” is unchecked but you still have this issue go to the “Security” and give all necessary permission to the user.

After all necessary configurations are done you will be able to perform SQL Server database backup successfully.

Источник

Понравилась статья? Поделить с друзьями:

Sql запрос завершен с ошибками

Sqlite ошибка database is locked

Sqlcmd при соединении с сервером произошла ошибка

Sql выдает ошибку при восстановлении баз

Sql строка подключения с ошибкой