Wednesday, January 17, 2007

Default databases in SQL Server 2005

Ok, I made a bad mistake yesterday, but at least I learned from it.

In SQL Server 2005, if you take your own default database offline for some reason, you can no longer connect to the server. You open up SSMS and when you try to log into the server, you get "Cannot Open User Default Database". So if you are the admin, you just locked yourself out of the system!

Yes, I did this to myself. At least it was on my test box.

The solution, which I found here, is use sqlcmd.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH
DEFAULT_DATABASE=master

2> GO

Thanks to "Lothan" who posted the solution.

1 Comments:

At Thu Sep 24, 04:22:00 AM , Blogger harry said...

Thank you very much for that lovely command :)

 

Post a Comment

Links to this post:

Create a Link

<< Home