sp_attach_single_file_db Error: failed with the operating system error 5(Access is denied.)

Question

I'm trying to use this DataBase that come with this sample project with from MS:

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

So after I downloaded the files: I need to attach the .mdf DataBase to my Instance of MS SQL 2008.

From Management Studio Attaching DataBase does not work and event using this command i receive 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? Thanks for your help!

Answer 1

Give full permission on folder in which you want to create the mdf file to the logon account with which the SQL Server service is running. Usually: NT Service\MSSQL$

You can look that up Control Panel-> Administrative Tools-> Services=> SQL Server -> Prperties-> Logon Tab-> Note the account

Answer 2

What operating system are you running on? Did you get an elevation prompt when you saved the file to the root of the C drive? What user account is SQL Server running under, and does it have permissions to read any files in the root of the C drive?

You might do better placing the file into %ProgramFiles%\Microsoft SQL Server\MSSQL10.<instance name>\MSSQL\DATA, alongside the other .mdf files that you know it can already read (adjust path above as necessary, but you hopefully get the idea).