Default fault

July 2020's T-SQL Tuesday is hosted by Kerry Tyler (b|t). Kerry describes pilots' use of plane crash reports to learn aviation safety lessons, and asks for similar – albeit hopefully lass catastrophic – tales of SQL Server-related disaster. Thanks for hosting, Kerry! At the time of writing, last month's host Kenneth Fisher's (b|t) health is improving, which is great news.

A number of years ago – and relatively new to SQL Server – I was in the process of migrating an operational data store (ODS) from one SQL instance to a new one. The ODS was used for online management reporting and ad hoc analysis by a team of specialists. The business was fairly nine-to-five for reporting, so the plan was straightforward:

• Backup the existing database
• Restore it to the new server (already built and prepped with logins, linked servers etc)
• Update connection strings in online reporting systems to point at the new instance
• Take the old database offline to flush out any missed/forgotten connections
• Run a few reporting application smoke tests
• Head home for tea and medals.

Migration day went pretty smoothly – it even looked like we'd found and amended every connection string likely to disable a downstream system. The instance from which we were migrating was a bit of a food court, so before signing off I opened SSMS to check on some other system issue… and found I could no longer log in.

Not an immediate out-and-out disaster – after all, I didn't need to log in to the old server right there and then – but still fairly uncomfortable. Of the many databases hosted by the server instance, the most important one – the one to which I connected most – had been the ODS database. To save me a couple of clicks when connecting I'd made this my default database, and when I took it offline I effectively disabled my SSMS connection.

The screenshot shows the sort of error message you'll receive in this situation. So what to do? Fortunately for me, IT sysadmins also insisted on being members of the SQL sysadmin server role. I had an anxious 30 minutes trying to track one down so she could log in and reset my default database, but at the end of that time I was logged back in and a lot more relaxed.

The lesson I learned at the time was this: Don't ever set your default database to anything other than master. I've since learned to use other tools to connect to a specific database, so this isn't the make-or-break rule I once thought it to be – but I'd argue that anything that produces unnecessary moments of anxiety is better avoided! Since then I just leave my default database as master.

And the other tools? As simple as sqlcmd. From the command line, run:

sqlcmd -S <server_name> -d master -E

to connect to the target server's master database. The -E flag uses your current Windows credentials – to connect with a SQL login, use

sqlcmd -S <server_name> -d master -U my_user_name

and enter your password when prompted. Once logged in, you can reset your default database and “re-enable” connections from SSMS:

ALTER LOGIN [my_user_name] WITH DEFAULT_DATABASE = [master]