10

I did a fresh SQL Server 2014 install. Next, I wanted to attach some databases from a previous SQL Server 2012 server. I copied all mdf and ldf files to the data directory, but when I tried to attach them, I got this error message:

CREATE FILE encountered operating system error 5 (Access is denied)
while attempting to open or create the physical file
'D:\SQLSERVERDATA\MSSQL12.BIDEV\MSSQL\DATA\controlling_demo.mdf'.
(Microsoft SQL Server, Error: 5123)

When I checked the file security settings (right click on file, properties, security), it said that I don't have read permission. The account I used to copy the files is in the administrators group. I have full control over the DATA folder and yet, just the files that I copied there are restricted.

When I try to re-apply the security rights on the whole folder, it says:

Failed to enumerate the objects in the container. Access is denied.

When I copy the files to somewhere else, I have access rights.

Question is, how can I get access rights for these files in the DATA folder?

11

Have you looked under Advanced Security Settings?

enter image description here

The arrows point to the modifications I've often had to specifically apply when installing SQL Server on Windows 2012 R2.

For the SQL Server Root Directory, I'll give full control to the SQL Server Process account.

Related official documentation:

Configure File System Permissions for Database Engine Access
Securing Data and Log Files

  • Thanks, it worked for me. I had to add the "Users" group to the security settings though. – frankieta Sep 20 '16 at 13:10
  • This still doesn't work for me... I tried everything, changing owner to System, adding MSSQL users, Users group, etc. I just can't have the DB create in the directory I want. – Ahmed Jun 21 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.