Wednesday, October 17, 2012

ATTACH DATABASE encountered operating system error 5 Access is denied Error

Issue:
While trying to attach an .MDF SQL Server Express Database to my current SQL Server Instance, I encountered with the below error. This was the error message received.

Attach database failed for Server 'HABEEB-HP\SQLEXPRESS'.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Unable to open the physical file "C:\testApp\App_Data\MyDB.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.00.5500&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476
------------------------------

Solution:
Googling quiet a bit mis led me to some version conflict reasons and file system security access issues. I tried to give full access to Network Service to the file. I tried to run SQL Server Service with Administrator Privilege from some suggestions online and nothing worked. Fiddling on it after a break I could figure out that its an SQL Server Management Studio (SSMS) privilege issue. I opened SSMS as Administrator and everything worked as a breeze.

8 comments:

  1. Thanks Nafi! I had the same problem and tried the same options you mentioned, but could not fix the issue. After your post, I ran SSMS as Administrator and everything went just fine!

    ReplyDelete
  2. That solved my problem too.....Thank You Nafi

    ReplyDelete
  3. wow..thanks..so simple..

    ReplyDelete
  4. I have been having access issues while attaching the database. But the problem got solved when I ran SSMS as an admin.thanks for sharing useful blog.

    ReplyDelete
  5. the most simpliest solution, i couldnt believe i've tried several things, changing regedit, re-set the permission etc.

    thanks mate

    ReplyDelete
  6. Amazing! Thank you so much!

    ReplyDelete
  7. Never thought of this. Thanks a lot. This wasted lot of my time... Thanks again

    ReplyDelete
  8. AFTER THREE DAYS OF WASTING TIME, YOUR APPROACH FINALLY WORKED FOR ME.

    ReplyDelete