how to reattach database 1

[bembden]

I detached the database from my SQL server running on my laptop to copy the files. I could not figure how to re-attach the database. When I booted the laptop this morning I got the following message. "A connection could not be established to XXXLAPTOP. Reason: Cannot open user default database. Login failed"


Any advice would be appreciated.
I am a novice at this so please be patient.

Bernie

 

[Catadmin]

In the security settings for the SQL Server, each user account is associated with a default database. If that default db gets deleted or removed, you are no longer able to connect until a SysAdmin or someone with the SecurityAdmin server role can change your login to use another default database.

Are you sysadmin?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[bembden]

Yes

I am the admin. I installed this sql app on my laptop

Bernie

 

[Catadmin]

Is this an application that is giving you this error or actual SQL Server Client tools (like Enterprise Manager, Query Analyzer or SQL Server Management Studio)?

If the later, what version of SQL are you using?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[bembden]

I am getting this error when I open Enterprise Manager 8.0


Bernie

 

[SQLBill]

How did you detach the database?

When you set up the login, what did you use for the default database? (Please don't tell me it's the detached one).

What type of authentication is your SQL Server set to use (please say Mixed Mode)?

If it's mixed mode, open up Query Analyzer using the SA account. Run the sp_attach_db command to attach your database.

I'm betting that you set the default database for the login you use to the one that you then detached. Your login is trying to log into the default database, but since it's not there it's failing. This is one reason I advise to set admin logins to default to Master or Tempdb. If SQL Server is running, those two databases HAVE to be there.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

You could also try (and I'm not sure this will work since you can't log into EM) deleting the registration for the server in EM, then adding it back and telling it to ask for credentials when you log in. Log in using the SA account there (if you don't want to do it via QA) and change your default database to another DB that you're sure isn't going to be detached or deleted.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[bembden]

I just now reattached the database. Some more info. I had two databases. Apparently I detached the database that my login defaulted to. This resulted in my inability to log in. Since the database was on my laptop, I did (what someone suggested) and logged in as the administrator. This allowed me to see my other database. I then changed my regular domain login to the master database. Rebooted on the domain and was able to re-attach the database through enterprise manager.

Not sure I have explained it properly.



Thanks again for your help


Bernie

 

[bembden]

Created another problem. Apparently I am no longer the database owner. Instead it is owned by a user. How can I change this? Is it possible that when I re-attached the database the ownership changed?


Bernie

 

[SQLBill]

sp_changedbowner. Check out the syntax in the BOL.

And yes to the last question, unless you specifically state who the owner is, it will default to the first one in the list that has access to the database.

-SQLBill

Posting advice: FAQ481-4875

 

[bembden]

Thanks Bill


That worked.

Bernie