XML FeedsI 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Excellent, never used the option button until today.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.