13

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

Error Message 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.

AlexMelw
  • 2,406
  • 26
  • 35
Jason Clark
  • 1,307
  • 6
  • 26
  • 51
  • please check https://dbamohsin.wordpress.com/2009/06/03/attaching-database-unable-to-open-physical-file-access-is-denied/ – Pedram Dec 25 '15 at 05:13
  • 4
    Don't add errors (or any other text either) as images. Search doesn't find them making the question useless for others – James Z Dec 26 '15 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. – vapcguy Mar 01 '19 at 17:55
  • Possible duplicate - https://stackoverflow.com/questions/2330439/access-is-denied-when-attaching-a-database - See JMarsch's answer to his own question. – vapcguy Mar 01 '19 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? – Neil Weicher Aug 20 '20 at 17:23

4 Answers4

31

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. enter image description here

And then give Full Control to user OWNER RIGHTS. enter image description here

permission for mdf file

Sameer Alibhai
  • 3,092
  • 4
  • 36
  • 36
maneesh
  • 919
  • 8
  • 6
  • 1
    Make sure to also update permission for the log file as well. – Pomster Oct 23 '17 at 09: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. – vapcguy Mar 01 '19 at 17:54
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. :)

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • 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. – vapcguy Mar 01 '19 at 17:52
6

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

Jimmy
  • 61
  • 1
  • 1
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/

Pobaranchuk
  • 839
  • 9
  • 13