SQL Server Error 5120 - failed to attach .mdf file

L

4

14

While attaching my .mdf file on my SQL Server database I am getting below error message:

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA' Operating system error 5: "5(Access denied.)".

I don't have my .ldf file of associated database. How can I attach my .mdf file without .ldf file? I am using SQL Server 2014.

Lampkin answered 25/12, 2015 at 5:9 Comment(5)

please check dbamohsin.wordpress.com/2009/06/03/… – Monetta 25/12, 2015 at 5:13

Don't add errors (or any other text either) as images. Search doesn't find them making the question useless for others – Unwarrantable 26/12, 2015 at 18:18

Shouldn't need the .ldf file. That's not the issue. Issue is likely permissions to the .mdf file. Once fixed, clicking to "Attach Database" should work. – Robi 1/3, 2019 at 17:55

Possible duplicate - #2330939 - See JMarsch's answer to his own question. – Robi 1/3, 2019 at 17:58

It looks like you are trying to attach it as a directory, rather than a physical file. Where is the filename? Are you attaching it via T-SQL? If so, can you specify the full T-SQL command? – Signora 20/8, 2020 at 17:23

B

9

That error is telling you that the account that is running the SQL Server service doesn't have permission to the mdf file. Fix the permissions and it should attach just fine.

Pro tip: pictures of text aren't as useful as the actual text. :)

Bouncer answered 25/12, 2015 at 8:2 Comment(1)

The way I found I needed to "fix permissions" wasn't how one might think - there was actually no "Security" tab on the files when you right-click the .mdf files. I had to re-navigate in Windows File Explorer to the location of the databases. When I did, I was prompted with the User Access Control dialog - clicked Continue - it must've applied permissions (though still no "Security" tab to confirm) because then I was able to attach the DB files. – Robi 1/3, 2019 at 17:52

M

33

GO to the folder where you have stored MDF file. You can either do this to the entire folder or the MDF/LDF individually. Select file/folder-> Right click ->click on "Security". Then "Edit'. If you dont have OWNER RIGHTS listed Click ADD and search for it, click OK.

And then give Full Control to user OWNER RIGHTS.

permission for mdf file

Migraine answered 27/2, 2017 at 21:6 Comment(2)

Make sure to also update permission for the log file as well. – Monogenetic 23/10, 2017 at 9:17

"Security" tab wasn't there for me, but fortunately closing the window, going back into Windows File Explorer to that path then prompted me with one of those User Access Control security dialogs and hit Continue. When I got into the folder, I then went back to SQL Server and was able to attach the database files. – Robi 1/3, 2019 at 17:54

B

9

That error is telling you that the account that is running the SQL Server service doesn't have permission to the mdf file. Fix the permissions and it should attach just fine.

Pro tip: pictures of text aren't as useful as the actual text. :)

Bouncer answered 25/12, 2015 at 8:2 Comment(1)

The way I found I needed to "fix permissions" wasn't how one might think - there was actually no "Security" tab on the files when you right-click the .mdf files. I had to re-navigate in Windows File Explorer to the location of the databases. When I did, I was prompted with the User Access Control dialog - clicked Continue - it must've applied permissions (though still no "Security" tab to confirm) because then I was able to attach the DB files. – Robi 1/3, 2019 at 17:52

K

7

Run SQL Server Management Studio as Administrator to attach the .MDF file. After that, no need to run SSMS as Administrator any more.

Kimberly answered 20/8, 2020 at 17:10 Comment(0)

M

0

Put your data file into the folder where you made permissions https://dbamohsin.wordpress.com/2009/06/03/attaching-database-unable-to-open-physical-file-access-is-denied/

Mercuri answered 26/5, 2019 at 10:42 Comment(0)

Recommended topics

Hot tags