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?
Best Answer
Have you looked under Advanced Security Settings?
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