SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.


Posted by thoover on 31 August 2011

I have been there!  It is our standard practice that all DBAs point to master.  I learned that one the hrad way years ago.  Thanks for the reminder.

Leave a Comment

Please register or log in to leave a comment.