SQL Server Performance Forum – Threads Archive

How to login when the default db was broken?

How to login a sql server when it’s default database was broken?
I got this error message:
Cannot open user default database'<ID>’.Using master database instead. Thanks for any help . Jelly.

Login as an administrator and run: sp_defaultdb ‘user’,’db’ They can then login. Why is the users database not available? Derrick Leggett

http://support.microsoft.com/default.aspx?scid=kb;en-us;307864&Product=sql to tackle the issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

DerrickleggettÂ©o
Thanks.But I can’t run sp_defaultdb when I can’t login.I got the right answer from satya.
A disk was failed so the database losed a file which named SubData.ndf.I have a full database backup and the newest of log backup.but I don’t know how to restore it.because the database can’t open.Can you help me ? Jelly.

You should still be able to log in as administrator or sa because the default database for the administrator is master. Have you lost the master database as well? Derrick Leggett

Yes,the master database is well.I seted the default db of sa for a user database,not master.so I can’t login when the user database was broken. Jelly.

Run the sp_defaultdb command using osql to change the default database then. You can then get back in and do whatever you need. You can find a cut and paste example of osql by looking at the Books Online or searching for it here. It’s just a command line utility to run SQL Commands. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

That is a good idea!
Thanks! Jelly.

Note that "isql -E" succeeds in it’s attempt to connect to the server, while "osql -E" fails even though both messages state that they are using the master database instead. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Why would you use -E when you are connecting as "sa"???? And if you are connecting as sa using osql and using the -d paramater, it’s not going to try to connect to validate the default database. It’s just going to run the script where you tell it to. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Where in the case of windows-only authentication, using SYSADMIN privileged login you must use -E option to resolve the issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>