SQL Server Operating system error 5: "5(Access is denied.)"

Question

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:\Murach\SQL Server 2008\Databases\AP.mdf". Operating system error 5: "5(Access is denied.)".

   CREATE DATABASE AP
      ON PRIMARY (FILENAME = 'C:\Murach\SQL Server 2008\Databases\AP.mdf')
      LOG ON (FILENAME =     'C:\Murach\SQL Server 2008\Databases\AP_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.

Sundown

If the backup file exists already, please make sure it does not have the "read only" attribute, in addition to making sure that the service has the write permission. – Ardin

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

Plum

Sometimes SQL server will run under a different user group other than Administrators Group – Crosse

Also, it may help to start SQL Server Management Studio as Admin, not just the server service. – Louls

For me the solution in the following link worked: https://mcmap.net/q/136782/-cannot-install-adventureworks-2012-database-operating-system-error-5-access-is-denied – Tailback

For me changing the SQL Server Agent Service account to Local System worked. – Garald

If your SQL Server instance is running under a service account (e.g., mine is MSSQL$SQLEXPRESS), it may not be obvious that you need to add the account to the folder permissions list as: NT Service\MSSQL$SQLEXPRESS – Bathetic

https://mcmap.net/q/136782/-cannot-install-adventureworks-2012-database-operating-system-error-5-access-is-denied worked for me too – Excruciating

@david.barkhuizen's suggestion fixed the issue for me. Try it before you fiddle with permissions. – Loydloydie

Adding full access "everyone" user for the windows folder, allows me to attach. – Frau

Perfect, I spend whole night and finally this thing solve my issue. – Acicular

I ran the management studio as administrator and it worked fine – Santa

Heads up: the database service requires the "Full control" permission. Simpy Read/Write, as mentioned in this answer will not work. – Whipstall

Here at the docs about Windows File Permissions and MSSQLServer learn.microsoft.com/en-us/sql/database-engine/configure-windows/… – Liquidambar

Answer 2

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.

Tranship

-1 Doing this goes against the whole idea of setting up this user in the first place. It's a security feature, and doing this circumvents it. – Electrobiology

Nicely done! Worked like a charm. SQL Server Express 2014 on Windows 10. – Richella

wow, i have tried many post' resolution and nothing worked, but your simple solution and step-by-step instructions solved me this issue. Thanks a lot. My environment is SQL 2012 on Windows 7 – Illegal

WOW.. Cant believe people just jumped into this "answer".. This might create a big security issue.. Otherwise, there wouldn't be a reason to set that user at first, right? Anyways, it is a "solution" for people who do not consider this as a security issue in their cases. – Preoccupation

@NappingRabbit - system account is way more powerful than a regular account which is a member of administrator account. The main reason is, system account has an access with FULL CONTROL to the HKEY_LOCAL_MACHINE\Security hive which is a protected part of the registry whereas regular administrators only have Write DAC and Read Control access for that registry hive. That is a registry that you would not want SQL server has an access to. hope it helps – Preoccupation

@Preoccupation what user would you suggest to have appropriate permission to write files? Any solution other than running [starting the app] in admin-mode would be great. – Tallyman

OR give the NT user permissions on the directory. – Ocher

This did not work for me I run SQL Server 2019 on Windows 10 – Leveret

Answer 3

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.

How

Answer 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 Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup (SQL 2012)
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup (SQL 2014)
C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Backup (SQL 2016)

Spiritless

@AdamLevitt do you have multiple versions of SQL installed or previously installed? Are you sure you are putting it in the correct location? – Spiritless

@hmd, Thanks I do. The fix was to export into the 2012 backups dir. – Mellow

This solution works in SQL Server 2014 as well. The path is C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup – Hagiolatry

I had a similar problem with MS SQL Express 2016, the solution was to put my .mdf file in the folder: C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Backup – Holt

Another option is to use TEMP folder, like "C:\Windows\Temp" - worked for me – Longsome

Answer 5

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:\Murach\SQL Server 2008\Databases" to the service account.

Similarity

Answer 6

I solved this problem by adding the "Full control" permission for both the .mdf and .ldf files for the "Users" group in Windows.

Francinafrancine

Instead of Users you can use Authenticated Users group – Phone

Answer 7

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.

Begonia

Answer 8

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

Testa

Answer 9

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)\MSSQL\BACKUP.

Hancock

Answer 10

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.

Historicity

Answer 11

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.

Thessa

Answer 12

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.

Lila

I did have just an MDF file, and this is The only solution that worked for me. Thanks. – Transit

Answer 13

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

System.Data.SqlClient.SqlError: 
Cannot open backup device 'C:\x\x\xxx.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.

Underwear

I tried the solution above to attach a database I moved to new location, but it didn't work for me, what actually worked was granting Full control permission to the domain account I use to log in to Windows – Die

Answer 14

Very Simple Solution.

1. Login with System admin
2. copy your mdf and ldf files in "C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA" Where all other data file recides.
3. Now attach from there it will work

Tender

You should not store data in the program files directory. The default location should be changed when you install SQl Server – Bogan

Answer 15

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.

Parmenter

Answer 16

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 Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\DATA

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

Torsion

Answer 17

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.

Font

Answer 18

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.

Braggart

Answer 19

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.

Crispas

The original question did not mention that a compressed database was applicable. The question pertained to security related access problem. How does this answer relate to that question? – Triclinium

Answer 20

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

1. EXEC xp_cmdshell 'net use X: \100.155.16.6\mystoragegatewayfolder xxmysuperpassxx /user:sgw-445577\smbguest /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.

Masoretic

Answer 21

I had the same issue if I wanted to prepare Backups in PowerShell in remote machine. The case was that SQL User didn't have permission to access the folder where I wanted to copy Backups. Adding permissions for authorised users for Backup folder resolved the issue. You can do that in PowerShell:

$BackupDir = "C:\Backups"
$permissions = Get-Acl $BackupDir
$permissions.AddAccessRule((New-Object System.Security.AccessControl.FileSystemAccessRule(
"Authenticated Users", "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")))
Set-Acl -Path $BackupDir -AclObject $permissions

After executing this command I was able to run:

Backup-SqlDatabase -ServerInstance $ServerInstance -Database $DatabaseName -BackupFile $BackupFile

Flossie

Answer 22

SQL Server installation is corrupt. Don't touch the permissions, simply repair the installation.

Crelin