Friday, December 09, 2016
Connecting to SQL Server when your default database is not available
One of our database on the development went in suspect mode today. This database was the default for a bunch of logins. These people could not login now. Someone needed to use a different database but he couldn’t login because the database that was in suspect mode was the default database for the login he was using.
I told this person to click on the Options button in the connection dialog and specify another database. I guess there was an misunderstanding because this person couldn’t get it to work. This means it is time for a blog post.
Let's take a look how this all works
Here is a script that will create 2 databases
Now create a new login named TestLogin with a password of Test. We are also adding the login we just created to the OopsBad database and we will make the login part of the db_owner role
Add the login we just created to the Good database as well and make the login part of the db_owner role
Make sure that you can login with the TestLogin account
Now that you know that you can login with the TestLogin account, use another account and put the OopsBad database in offline mode
Take the database online
Now if you try to login with the TestLogin account, you will see the following error
Here is what you need to do, on the connect to server window, click on the Options button
One the next screen, click on the Connection Properties tab, do not click on Browse server... from the drop down. If you click that you might have to wait for a long time
Instead just type in the database name, in our case the database name is Good
Click on Connect and you should be in.... That's it, pretty simple, you just have to know where to find it.