Connecting to SQL Server when your default database is not available
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

CREATE DATABASE Good
GO
 
CREATE DATABASE OopsBad
GO



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


USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'Test', DEFAULT_DATABASE=[OopsBad]
USE [OopsBad]
GO
CREATE USER [TestLogin] FOR LOGIN [TestLogin]
GO
USE [OopsBad]
GO
ALTER ROLE [db_owner] ADD MEMBER [TestLogin]
GO



Add the login we just created to the Good database as well and make the login part of the db_owner role

USE [Good]
GO
CREATE USER [TestLogin] FOR LOGIN [TestLogin]
GO
USE [Good]
GO
ALTER ROLE [db_owner] ADD MEMBER [TestLogin]
GO



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

ALTER DATABASE OopsBad SET OFFLINE





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.

