THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Check the Default Database Setting for your Admin Accounts

Last weekend, as I was waiting for the start of the performance of the Cleveland Orchestra at its summer outdoor venue, Blossom Music Center, a client called with a problem. The databases on his principal production server had failed over to the mirror server, and now he couldn't connect to the principal server.

After trying a couple of things we were able to determine that the principal server was indeed up and running, and on the mirror (now principal) server, he could see that mirroring was still working properly, and the databases were synchronized. I walked him through the process of manually failing the main database back, and the scripts I'd implemented to automatically fail the rest of the associated databases over kicked in, and all the databases were back and functioning on the original server.

The reason he couldn't log into the principal server was that the administrative accounts had their default database setting set to the main application database. Because that database was in a restoring state the login attempt couldn't connect to that database, so the login failed.

This is why your administrative accounts should always be set so the default database is 'master'. If a user database fails the account can still connect to the server and assess the problem before bringing the databases back from your DR solution, whatever it may be.

Sometimes it's the little things that'll get ya!


P.S. The good news is that on the connect dialog in SSMS there's an Options button that allows you to specify the database to open when you connect, and this overrides the default database setting, so he was able to get in that way.

Published Wednesday, August 31, 2011 4:30 PM by AllenMWhite



jonmcrawford said:

Did you fly off the Handel? Or were you able to sit Bach and enjoy the Mozart?

August 31, 2011 4:18 PM

AllenMWhite said:

Very good, Jon! Actually, the Mozart/Bach/Handel/Mendelssohn performance was Saturday. This happened Sunday, when they performed Britten, Weber, Bernstein and Elgar.  Both nights were excellent, though.

August 31, 2011 4:44 PM

Tempdb, too said:

Why not use tempdb?  That way, create scripts accidentally run in the default database don't leave garbage hanging around past the next restart.

September 6, 2011 5:30 PM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement