Attach database failed for server

Question

I am trying to attach a database in SQL Server 2008 and I have .mdf file of database but I am getting an exception of

Attach database failed for Server 'Bilal-PC'. (Microsoft.SqlServer.Smo)

Unable to open the physical file "E:\Intelliscence\Backups\test (2011年07月19日)\Database\QBankDB.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

I searched the solution of this problem and found one solution of access rights of database file but database file has already full control access rights. Now I want to attach this database or want to get script of this database. But How?

I know this is a duplicate question but all the solutions given in answers are not working for me that's why I am posting this question.

Please help me.

• Billz: I know the tags are unrelated but C# and asp.net developer use the SQL Server
• RThomas: Are the files marked read only?
• Aaron Bertrand: @RThomas good point, or already attached to another SQL Server instance.

Answer 1

Try to run the Management Studio as Administrator and attach the database.

Right click on Management Studio Properties and Run As Administrator.

Hope it helps you.....

• Aaron Bertrand: I don't think this is correct. If the SQL Server service account does not have access to the folder(s), it doesn't matter who you launch SSMS as.
• Christoph: It does not seem correct but it did the trick. Network Services had access but the Management Studio somehow could not access it when not started as administrator.
• Ahmed Abobakr: I just tried your solution and it works :) thank you
• Doc: This solution worked for me as well! What a bug! Has this erroneous behavior been documented somewhere?

Answer 2

The error message seems pretty straightforward: Access is denied. You need to make sure the SQL Server service account has adequate permissions on E:\Intelliscence\Backups\QBank (2011年07月19日)\Database,円 or move the MDF/LDF files for the data/log folders for the instance (where this shouldn't be a problem).

• David Anderson: For anyone who has addressed permissions and it still doesn't work, see the correct answer below about SSMS as Administrator: stackoverflow.com/a/17159894/90476
• David Anderson: Your local account matters when using Windows auth. This is total speculation, but I'd guess what's happening is that the attach command accesses the file under the context of the user rather than the SQL Server service. The file in the OP's case and my case were created under a directory hierarchy created directly under the drive root, which with default permissions inherited means that a non-admin would not have full control. Of course, you could tweak perms, but I don't think it's obvious what user and perms are needed in the first place and running SSMS as admin avoids all this.

Answer 3

u should edit security of .mdf & .ldf files to full control like this pic enter image description here

Answer 4

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Instructions for attaching database to the SQL %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

• Run "SQL server 2014 management studio".

• Login to \SQLEXPRESS.

• Right click on the Databases folder and select "Attach".

• In the opened UI select Add.

• Browse to the folder where the .mdf and .ldf files are located.

• Select the .mdf file. Notice: The .mdf and .ldf files you've selected will be deleted, so make sure you make a copy of those files beforehand.

• Click on the "Attach As" tab and type the new database name.

• Make sure the "Current File Path" are correct for the ldf and the mdf files.

• Click ok.

Answer 5

Run the Management Studio as Administrator and connect server with windows authentication.

Try to attach the database.

I have done with this.....

Answer 6

I think this has nothing to do with permission, I exactly don't know the entire scenario, but for example, if you are trying to attach an mdf from SQL server 2008 to SQL server 14 or something else, just copy the MDF and LDF file from C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA and place them in the C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA and then try using the regular attach method, and you should be fine

Answer 7

It might be because of

• Default Location of SQL Data Files Has Changed
• SQL Server Resets File Permissions when Database is Detached

Try any of the following.

• Run SQL Server Management Studio as an Administrator. (right click-> run as administrator).
• The account that I was using had full control NTFS permissions to the mdf and ldf files. However, it had those permissions through group membership (the Local Administrators group had permissions, and my account is a member of local admins). So Give Full Access for *.mdf / *.ldf files.

Please refer here for more information.