Changing the Default DB for a Login–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I got into a bit of a pickle. I was detaching some databases for a demo, which is something I do periodically to make it easier for someone to see what’s doing on. The database detached fine, and I ran my demo.

Then, as a sysadmin, I right clicked to attach a database back.

2017-10-23 15_59_17-SQLQuery6.sql - (local)_SQL2014.SimpleTalk_1_Development (PLATO_Steve (57))_ - M

and all of a sudden I get this error:

2017-10-23 15_59_08-Microsoft SQL Server Management Studio

Interesting. I could have attached the database back from the command line, or with dbatools, but I thought this was interesting. As I go to the attach dialog, a new connection is made. However, in this case, the default database for my sysadmin account was the one I’d detached.

Fortunately, this is easy to fix. First, I opened a query window with master as the specific database:

2017-10-23 16_01_20-Connect to Database Engine

Then, I have a couple options to reset my default database. For old SQL Servers, many of you might have used sp_defaultdb. That’s marked as a deprecated procedure, so ALTER LOGIN is the new way. The syntax uses the WITH to include various options. In my case, I needed the DEFAULT_DATABASE item. This was my code:


If you are on an older version, something like this will work:

exec sp_defaultdb @login = ‘Steve’, @defaultdb = ‘master’

Once that was done, the GUI dialog worked. A quick and easy fix in this case.


As soon as I found the error, I knew what was happening. Resetting the default database took less than a minute, but I decided to spend 10  grabbing a few screenshots and putting this post together.

You could do the same thing. Show that you can recover from errors.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.