Unable to open the physical file trying to attach AdvntrWrks2008 DB

Question

Unable to open the physical file trying to attach AdvntrWrks2008 DB

jb_sql_geek

SSC-Addicted

Points: 419
More actions
May 9, 2012 at 10:24 am

#256279

Hello SQL server colleagues, I'm trying to attach the Adventureworks2008 DB to my local instance and get the error below:
I have full control of all the SQL server directories, what gives? ARRGGGHHHH!
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'AC44LKQ1\MSSQLSERVER2008'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&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:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents". Operating system error -2147024891: "0x80070005(Access is denied.)".
A file activation error occurred. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Could not open new database 'AdventureWorks2008'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 5120)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

When you open SSMS, Run As Administrator.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thomas Stringer SSCarpal Tunnel Points: 4038 More actions · Answer 2

GilaMonster (5/9/2012)
When you open SSMS, Run As Administrator.

Why would it be running under the security context of SSMS? Wouldn't it be a permission issue with the SQL Server service account on that file?

Twitter: @SQLife
Email: sqlsalt(at)outlook(dot)com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Thomas Stringer (5/9/2012)
GilaMonster (5/9/2012)
When you open SSMS, Run As Administrator.
Why would it be running under the security context of SSMS?

It's not. The attach itself runs under the SQL Server service account, but there are some peculiarities around attaching using the GUI that require the user to have access to the file as well.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thomas Stringer SSCarpal Tunnel Points: 4038 More actions · Answer 4

GilaMonster (5/9/2012)
Thomas Stringer (5/9/2012)
GilaMonster (5/9/2012)
When you open SSMS, Run As Administrator.
Why would it be running under the security context of SSMS?
It's not. The attach itself runs under the SQL Server service account, but there are some peculiarities around attaching using the GUI that require the user to have access to the file as well.

Oh, ok. I see. Good information, Gail.

Twitter: @SQLife
Email: sqlsalt(at)outlook(dot)com

jb_sql_geek SSC-Addicted Points: 419 More actions · Answer 5

My id is in the administrator group. And, I was able to attach the other sample DB's just fine. This is a pain! thanks for listening.....

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Yes, so was mine when I ran into this. Welcome to UAC. Give it a try, nothing to lose.

If that still doesn't work, check that the folder mentioned exists and that the SQL service has full control on the folder (its for the filestream files)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jb_sql_geek SSC-Addicted Points: 419 More actions · Answer 7

Thanks for the response....

SQL service has full control on the folder (its for the filestream files)

Which id is this? All windows id's have full control on the directory...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Whichever account SQL Server is running as.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jb_sql_geek SSC-Addicted Points: 419 More actions · Answer 9

GOT IT! Thanks to a little help from my friends.....

control panel > admin tools > services > SQL Server (MSSQLSERVER2008) > right click properties > logon > as local system acct.

THANKS AGAIN! Now I can dive further into the text book - WOOHOO!

😀