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!

Solution 1

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

Solution 2

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

Comments

[GibboK]

Damien many thanks, i moved the file in DATA folder and now it is working. I'm pretty new on MS SQL could you tell me what is for the DATA FOLDER exactly?

[Damien_The_Unbeliever]

@GibboK - The DATA folder is the default folder where SQL Server will create new databases (if you execute a CREATE DATABASE statement and don't tell it where to place the files), unless or until you change the default. For production installations of SQL Server, you normally place the .mdf and .ldf files on other drives, and leave few if any databases in this folder, but for a small/play installtion, I'd say put all of the databases in there.

[Yagami Light]

Can you please add some link to make your answer stranger

[Oky]

I think it was Brent Ozar, but indexes point to the PK. When you drop PK Then you have a btree with a new identifier. so all indexes get rebuilt with that Id. Then you do something crazy and create a new PK, just to rebuild them again.