I was restoring a TB+ sized database on our staging database today. Someone needed to use a different database but he couldn’t login because the database I was restoring was the default database for the login he was using. I told him to click on the Options button in the connection dialog and specify another database. I guess there was an misunderstanding because he couldn’t get it to work. This means it is time for a blog post.

First, let’s create two databases

CREATE DATABASE Test1
GO

CREATE DATABASE Test2
GO

Now create a new login named TestLogin with a password of Test

USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'Test', DEFAULT_DATABASE=[Test1]

Add the login we just created to the Test1 database and make the login part of the db_owner role

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

Add the login we just created to the Test2 database and make the login part of the db_owner role

USE [Test2]
GO
CREATE USER [TestLogin] FOR LOGIN [TestLogin]
GO
USE [Test2]
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 Test1 in offline mode

ALTER DATABASE Test1 SET OFFLINE

Now if you try to login with the TestLogin account, you will see the following error

Login failed for user ‘TestLogin’. (Microsoft SQL Server, Error: 18456)

Here is what you need to do, on the connect to server window, click on the options button

That will open the Connection Properties tab

Whatever you do, do not select Browse server… from the connect to database option, if you do that you will get the following error

Just type the database name of the database that is not offline instead

As you will see you can connect without a problem now

Hopefully this will help someone else in the future also