Trouble Attaching a Database

Question

Trouble Attaching a Database

caselig

SSC Veteran

Points: 229
More actions
June 6, 2009 at 6:39 pm

#138376

I am trying to attach sample databases from the book Murach's SQL Server 2005 for Developers but the files don't attach. I keep getting an error:
Attach database failed for Server 'ME\MSSMLBIZ'. (Microsoft.SqlServer.Smo)
Additional information:
An exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Unable to open the physical file "C:\Murach\SQL Server 2005\Databases\AP.mdf".
Operating system error 5: "5(Access is denied)". (Microsoft Sql Server, Error:
5120)
Can anyone help me?

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

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

The error is permission related do you have access to the SQL Server folder? You also need the LDF file if not then you need to post again so I can show you how to attach without LDF. So put both LDF and MDF in the location below before going to SMS to right click and attach.

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Kind regards,
Gift Peddie

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

Does the SQL Server service account have full rights to the database files? The error implies that it doesn't.

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

caselig SSC Veteran Points: 229 More actions · Answer 3

So I moved the files to the directory you specified.

Now I get an error that the file is read only. Error: 3906

caselig SSC Veteran Points: 229 More actions · Answer 4

How can I find out it I server rights.

I am logged in on my local computer using Windows authentication.

Chuck

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

caselig (6/7/2009)
So I moved the files to the directory you specified.
Now I get an error that the file is read only. Error: 3906

That could mean different things if you are in Vista in SMS right click and run as Admin, or the files are read only which means you have to change the files attribute. And the last go to configuration manager and make sure SQL Server service is running with either local system account or domain user account with permissions to the SQL Server folder in programs.

Kind regards,
Gift Peddie

WayneS SSC Guru Points: 95409 More actions · Answer 6

I have noticed on my Vista computer that when I want to attach a database, that I need to run SSMS as administrator (right-click on SSMS, select Run As Administrator). Otherwise, I do get access errors when trying to attach the db. See if this helps you out.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

caselig SSC Veteran Points: 229 More actions · Answer 7

Thanks everyone for your suggestions:

I did go into the properties of the SQL Server Management Studio and changed the properties to Run as Administrator.

I check the actual file and it's properties and not listed as read-only.

But, lo and behold, the file is attached, but listed as read-only.

I would like the ablility to change things. Can I get this file to be editable?

Thanks,

Chuck

Gift Peddie SSC Guru Points: 73570 More actions · Answer 8

In SMS right click on the database go to properties and then options close to the end of options you will see State you will see Database Read only it will be true change it to false, I am adding an attachment that will show what I am talking about.

Kind regards,
Gift Peddie

caselig SSC Veteran Points: 229 More actions · Answer 9

That was it. Thanks a bunch. Now I can continue learning Sql Server.

Chuck