SQL Server Database Engine
Cannot connect to the server detaching the default database
Question:
Hi all,
I want to move the database files to another location so I detached the database and thought I would be able to reattach it from the new location. Unfortunately, I cannot connect to the server anymore. When I try to connect in the Management Studio, I get the following error:
TITLE: Connect to Server
------------------------------
Cannot connect to ServerName.
------------------------------
ADDITIONAL INFORMATION:
Cannot open user default database. Login failed.
Login failed for user ServerName\UserID'. (Microsoft SQL Server, Error: 4064)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Any tip will be greatly appreciated.
hz
Answer1:
You need to change your default database - check out this thread - Laurentiu outlines how to change the default database via SQLCMD (a command line utility installed with SQL 2005). If you're on SQL 2000, you can do a similar thing with OSQL. Here is the thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=167068&SiteID=1
Aaron Alton
Answer2:
If you are using SSMS, you can also click "File - New - Database Engine Query". Select server and authentication, then click "Options >>" button to be able to select the database you want to connect to.
While in the other database, you can change the default database executing the stored procedure sp_default, if you need to.
Attach the database and change it to be the default for your loggin.
AMB
Answer3:
Whenever you move the database using detach/attach option the default databases will get misplaced even after you attach the database. So as a best practice you need to make a note of all the default databases for all the logins before detaching the database and after attaching it you need to cross verify the same to ensure that everything is fine. Adding to the above solutions you can also refer the below KB,
Answer4:
Hi Aaron, AMB,
Thank you so much for the tips. I tried Laurentiu's approach suggested by Aaron and had a bit trouble, then I saw AMB's suggestion and thought it was easier and it worked. A minor correction: the sp is sp_defaultdb, not sp_default at least for SQL 2005.
hz
Answer5:
Thanks, Deepak, for the good suggestions. I will not make the mistake again.
hz