How can I regain access to MS SQL Server after default database for a user is taken offline?

Question

Act in haste...

In the process of off-lining a database for backup I lost connection, the only user I had details for was set to use the database which I had just off-lined (thankfully not the master database) as the default database...problem as you can see.

In looking about on stack overflow I was unable to find a solution for my problem.

After a bit of digging about I remembered the command-line tool sqlcmd.

Answer 1

Repent at leisure:

Well the solution to the problem was pretty straightforward:

• Open up a command-line ( Start->Run->cmd then hit Enter)
• In the command-line type the following (substituting correct values where needed)
  • sqlcmd -S myserver\sqlexpress -d master -U myusername -P mypassword which starts a command-line connection to the database specified after -d
  • Note: ensure the switches are typed as above as unusually MS actually bothered with case sensitivity, so -s/-S, -d/-D, -u/-U and -p/-P are not interchangeable.
• once the connection to the database is established the window will display a connection prompt similar to:
• Once you have the correct prompt, type the following in:
  • alter login myusernamehere with default_database = master to set the default database for your user to master
  • On the next line type go and press enter . . . and that should be your user set to using the master database as its default database to connect to. It should appear on-screen to look like this:
• Now you can connect with MS SQL Server Management Studio as normal and get the lovely graphical interface to use for your database admin tasks.
• Carry out your management tasks and bring the database back on-line as you need/see fit
• Set the default database for your user back to the database you have just brought back online, by changing the name of the database in the previous command to the name of the database you just brought back on-line, looking something like:

And that should be you set. I know this is an unlikely scenario for most, but should it happen to you, hopefully this will be of some use in restoring your normal access and getting the database back on-line.

Answer 2

The other option is to click the "Options" button when logging into SSMS. There, you can specify which database to connect to. So as long as you have access to one of the other databases, such as master, you should be able to connect.