HI ,

This is a problem I encountered when I had to detach a database file (type .mdf):

1) I went to the MS SQL Management Server Studi and detached my database file successfully from a connection called Workhorse.

2) I needed to place the .mdf database file into a zip file in order to put it on a remote server. I did this using Shared Portal. This was also successful

3) However when I tried reattaching the database file, I got this error:

CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "C\Program Files\MSSQL Server\MSSQL\Data\<databasename>.mdf'

Q) The database file and log file (ldf) exist in the correct directory so I don't know what happened. Can any one help?

Thanks much

Tonante

Dear Tonate,

Please, take a look on the following link and might be it will help you :)

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=120536&SiteId=1

• Unmarked as answer by Naomi NModerator Thursday, May 15, 2014 7:06 PM

Hi Tonate,

I got the solution for this. I proud this is my first blog to provide the answer on internet

I got the same error too:

CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "C\Program Files\MSSQL Server\MSSQL\Data\<databasename>.mdf'

the solution is:

set the Operating system permission on that .mdf file to full permission to 'Everyone' in new server.

this will helps me to attach the database in other server..

Have a Great Day..

vino...

• Proposed as answer by Kiran Cherupally Tuesday, October 20, 2009 12:35 PM

I had same issue on Vista machine. I run "SQL Server Express Management Studio" as "Run as administrator" and it worked.

• Proposed as answer by Jonathan Gardner - MSFTMicrosoft employee Tuesday, September 01, 2009 8:56 PM
• Marked as answer by Kalman TothModerator Wednesday, May 28, 2014 12:13 AM

I had same issue on Vista machine. I run "SQL Server Express Management Studio" as "Run as administrator" and it worked.

• Marked as answer by Kalman TothModerator Wednesday, May 28, 2014 12:13 AM

• Edited by Kiran Cherupally Tuesday, October 20, 2009 12:41 PM refined

 

 

 HI ,

 

 This is a problem I encountered when I had to detach a database file (type .mdf):

 

 1) I went to the MS SQL Management Server Studi and detached my database file successfully from a connection called Workhorse.

 

2) I needed to place the .mdf database file into a zip file in order to put it on a remote server. I did this using Shared Portal.  This was also successful

 

3) However when I tried reattaching the database file, I got this error:

CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "C\Program Files\MSSQL Server\MSSQL\Data\<databasename>.mdf'

 

Q) The database file and log file (ldf) exist in the correct directory so I don't know what happened. Can any one help?

 

 Thanks much

 

Tonante

 

• Proposed as answer by aydin secer Wednesday, March 31, 2010 11:09 AM

Go to the xx.mdf and xx_log.ldf files where database is located and give Everyone user to full access right for these files

this is exact solution.

• Proposed as answer by kjshaju Friday, May 14, 2010 12:32 PM

Right-Click on the SQL Server icon and select run as administrator.

I have the same issue and I followed everything you said in this forum, I change the permission to every both mdf and ldf file. Aside from that I also run the MS SQL as administrator but the problem still persist .. anyone  has other solution?

Thanks,

SQL Server has, for many years, been picky about the commands you issue when attaching database files.  Problems can arise when you want to attach multiple copies of essentially the same database, say for test and comparison purposes.

Specifically SQL Server is not 100% perfect at guessing what you intend to do, especially if you say detach a database file and then attempt to attach another database file which once had the same name as the one you just detached.  SQL Server will likely attempt to re-attach the first one.  This is because the original file names are embedded within the mdf.  If you look closely and expand the exact file location, you can enter the correct file name manually as part of the attach process.

What I have found happens is the reason an mdf will not open is because it is already open in SQL Server, and you need to check to see the exact one you have attached, you might (in error) have the wrong mdf associated with the database name, it is terribly easy to get the names swapped around.  Check the database properties to see the exact name of the mdf and ldf files.  A clue is that you can't rename the mdf in the Explorer.  If so, almost certainly it will be SQL Server that is holding the file open (what else have you got that will open an mdf?).

HTHs

1. Exit from SQL Server 2005\close Management Studio(all instances).
2. Right-click the drive in which SQL Server is installed usually C: or the drive where your .mdf and .ldf files are saved. For example, C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data the default location of the default databases.
3. Right-click C: and click Properties.
4. In the Properties dialog box, select the Security tab. (If security tab is not visible, as usual, then first remove the use simple sharing option, see below for steps).
5. In the Select Group or User Names box view that your SQL login is listed or not. For example, if you use Myserver\sa as the SQL server authentication mode then Myserve\sa should be listed in this box. If it is not then add the name.
6. Click the Add button. Enter the name you used to login to SQL Server for example sa.
7. Click Check Names to verify that the name you are specifying does exists as SQL Server user. If it is not, click Advanced in the Select Group or User Names box.
8. Click the Locations button and select your SQL Server name. Click Find Now. All users of the SQL Server are listed in the box.
9. Select the user, preferably your SQL Server authentication account. Click OK.
10. In the Group or User Names box, select the user name you selected in the above step and give full control permission to the user. Click OK.

1. Open My Computer.
2. Click Tools and select View tab.
3. In the Advanced Settings box, scroll down to bottom and clear the checkbox named Use simple file sharing (recommended).
4. Click OK. 

Hi Tonate,

 

I got the solution for this. I proud this is my first blog to provide the answer on internet

 

I got the same error too:

 

CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "C\Program Files\MSSQL Server\MSSQL\Data\<databasename>.mdf'

 

 

the solution is:

 

set the Operating system permission on that .mdf file to full permission to 'Everyone'  in new server.

 

this will helps me to attach the database in other server..

 

Have a Great Day..

 

vino...

Thanks Vino. :)

VINO,

YEAH!!! This fixed my issue and saved me about 12 hours of rebuilding!!!! Thanks!

You could also run into the errors if that .mdf file was being used by another application:

TITLE: Microsoft SQL Server Management Studio

------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

CREATE FILE encountered operating system error 32(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\ds10-cat.mdf'. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

- Give necessary permissons (modify, write etc.) to user who you are using to create DB with.

- Make sure Sqlserver Windows service log on type is Local System or someone else who have necessary rights on SQL Folders.

Windows authentication trick worked for me.  which is ok to start work :)

Thanks to all.

Start>Run>type MSCONFIG,

under Tools Tab, find the option to Change or Disabe UAC.  Done

I rebooted then attach db worked! 

Struggled with this on a Server 2008 box with SQL 2008 R2.  My story was i had SQL Instance with wrong collation so had to reinstall (used to be called rebuildm, (Microsoft, if you're listening, STOP changing names of things please!) and then re-attach the 81 dbs!  After changing the collation trying to reattach the db's i got this error.   Definitely a permissions thing, going on here, nothing worked until i turned off User acces control, UAC, eg. 

Start>Run>type MSCONFIG,

under Tools Tab, find the option to Change or Disabe UAC.  Done

I rebooted then attach db worked! 

Suppose you could then enable UAC after to keep server safe....

• Proposed as answer by Leelondon Monday, May 19, 2014 2:42 PM
• Edited by Leelondon Monday, May 19, 2014 2:44 PM

Hi Tonate,

Thanks for the solution, its really works.

Hema Mestha

Hi Tonate,

 

I got the solution for this. I proud this is my first blog to provide the answer on internet

 

I got the same error too:

 

CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "C\Program Files\MSSQL Server\MSSQL\Data\<databasename>.mdf'

 

 

the solution is:

 

set the Operating system permission on that .mdf file to full permission to 'Everyone'  in new server.

 

this will helps me to attach the database in other server..

 

Have a Great Day..

 

vino...

I had almost the exact same error - created a database in SQL 2008 Express and moved it to a SQL 2008 R2 Standard database. This solution worked for me. Thanks Vino.

All the best

Dave

Thanks a lot Tonante!

• Edited by alexcalm Tuesday, March 20, 2012 11:34 AM

I got the solution for this. I proud this is my first blog to provide the answer on internet

I got the same error too:

CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "C\Program Files\MSSQL Server\MSSQL\Data\<databasename>.mdf'

 

the solution is:

set the Operating system permission on that .mdf file to full permission to 'Everyone'  in new server.

this will helps me to attach the database in other server..

 

Have a Great Day..

 

vino...

Thanks Vi

Thanks for the useful hints on this thread, but you shouldn't need to set permission for EVERYONE. Instead, figure out which system account your SQL Instance is running as, using SQL Server Configuration Manager, and looking in the Log On As column.

Give this account permissions and you should be good to go. It worked for me.

• Proposed as answer by Justin Tolchin Tuesday, May 07, 2013 6:59 PM
• Marked as answer by Kalman TothModerator Wednesday, May 28, 2014 12:13 AM

any solution else? (Hurry)

but I did do it in Administrator!

so what 's Going on ?

Hi Tonate,

I got the solution for this. I proud this is my first blog to provide the answer on internet

I got the same error too:

CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "C\Program Files\MSSQL Server\MSSQL\Data\<databasename>.mdf'

the solution is:

Thanks - You saved my day! Google is my friend and you to!

I had faced this Problem Also to reattach DB to same Server After De_attach it

Just do this

Copy mdf file and log file to a new folder with any name "ensure that Everyone has permission (Full control on it)"

then you can Attach it.

Hi jgardner04,

Thank you. It worked for me.

I had this error, running Management studio as administrator didn't solve. Then, when I looked at the NTFS security tab of the detached database, the NTFS security was all removed.

Here is what I did;

Take the onwership of the files, both mdf and ldf files. 

Give Full Controls to following 3;

SYSTEM

Administrators

servername\SQLServerMSSQLUser$... (looks like some sql service account)

Then, run Management Studio as administrator, I was able to attach the db. 

Hi 

I try it and worked 

1. change owner permission files of mdf and ldf  

2. run Sql Server Management Studio with run as Administrator

3. Attach...