Sp_attach_single_file_db Error: operating system 5 failed (access is denied.)

Question

I am trying to use this database that comes with this sample project with MS:

http://code.msdn.microsoft.com/ASPNET-Web-Forms-6c7197aa/sourcecode?fileId=18930&pathId=365206059

So, after I uploaded the files: I need to bind the .mdf database to my instance of MS SQL 2008.

From Management Studio, attaching a DataBase does not work, and an event using this command gets the same error:

sp_attach_single_file_db 'School.mdf', 'C: \ School.mdf'

---

ERROR:

Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file "C:\School.mdf" failed with the operating system error 5(Access is denied.). 

Any idea what is wrong? Thank you for your help!

Answer 1

What operating system do you work on? Did you get a hint when you saved the file in the root directory of drive C? What user account is running SQL Server and does it have permissions to read any files in the root of the C drive?

It’s better to put the file in %ProgramFiles%\Microsoft SQL Server\MSSQL10.<instance name>\MSSQL\DATA along with other .mdf files that, as you know, can already be read (if necessary, adjust the path above, but you hopefully get this idea).

Answer 2

Grant full permission to the folder in which you want to create the mdf file for the login account that the SQL Server service works with. Usually: NT Service \ MSSQL $

You can see that Control Panel-> Administrative Tools-> Services => SQL Server → Prperties-> Login Tab-> Account Note

Answer 3

I had this problem and all the solutions on the Internet were misleading.

The solution was to open SSMS as an administrator. In my opinion, try this first and then try all the other solutions.

Answer 4

Moving it to the DATA folder basically solved the resolution problem. Another solution is to change the access rights to the file (or files) and, thus, the presence of the database in another folder. I just encountered a similar permission error "CREATE FILE encountered an operating system 5 error (Access is denied.)", Trying to connect DB in SQL Server 2012 Express in Windows 7.

The solution I made is the following: 1) Rich-click on each MDF, LDF and NDF file to get the menu → select properties 2) Add users for the computer and give them full control (instead of all, dangerous ones)

After that, I could connect the database.

However, even if it is not moved to the DATA folder, you must go to a subfolder instead of C: \ root (example: C: \ mydb).

Answer 5

create DATABASE newDataBaseName

on (FILENAME = 'C: \ Program Files (x86) \ Microsoft SQL Server \ MSSQL12.SQLEXPRESS \ MSSQL \ DATA \ filename.mdf'),

(FILENAME = 'C: \ Program Files (x86) \ Microsoft SQL Server \ MSSQL12.SQLEXPRESS \ MSSQL \ DATA \ filename_log.ldf')

FOR ATTACH_REBUILD_LOG;

GO

Just like the above Damien puts the mdf files in the place where your other mdf files are located. For me, C: \ Program Files (x86) \ Microsoft SQL Server \ MSSQL12.SQLEXPRESS \ MSSQL \ DATA

Answer 6

In my case, I found that a directory search is necessary for a group of computers / users in read / list permissions. Although the service and the actual user performing the recovery (in my case) had full permissions, which were important. Security I hate.