"Cannot open user default database. Login failed" after altering MDF and LDF locations and bringing DB offline

Question

I was attempting to move my database to a separate hard drive on the server, so I ran the following commands in the following order (I was following this question : How to change location of SQL Server 2012 database which is already configured with a merge replication):

use master;
ALTER DATABASE db_name
MODIFY FILE (NAME = db_name, 
 FILENAME = 'E:\Data\db_name.mdf');

ALTER DATABASE db_name
MODIFY FILE (NAME = db_name_log, 
 FILENAME = 'E:\Data\db_name_log.ldf');

Here is where my mistake happened: I either did one or both of the following mistakes:

• I may have input the incorrect Name parameter above (Would the mdf/ldf files be dropped in this case?)

...and/or...

• I also did not do step 2 in the answer referenced above (Stop log reader agent, distribution and merge agent (if they are running).

Then I brought the database offline:

alter database db_name set offline

Now when I try to connect to the database server, I get the error I described in this question. How can I remedy this? Do I need the sa login? Or is there something else I can do?

Thank you.

Other users are unable to connect to the database also, getting the same error. Did I actually corrupt the master db somehow?

I've been researching the causes of this error and they all seem to point to the need to restore access using sa login or something to that effect.

• Jonathan Fite: Were you attempting to move master? In any case, the error you get is because the default database for the user/login in question is the database you marked as offline. Logging in as sa should allow you to proceed with the remainder of your maintenance. Alternatively, in the advanced options for logging into SSMS, you should be able to specify a database to connect to, use master or another database you haven't moved and you should be able to get in.
• cdabel: I can't upvote the comment, but the comment from @JonathanFite above very concisely explains the reason for the error.

Answer 1

I may have input the incorrect Name parameter above (Would the mdf/ldf files be dropped in this case?)

This would not have dropped the files, if the NAME values weren't correct, the command would have failed.

I also did not do step 2 in the answer referenced above (Stop log reader agent, distribution and merge agent (if they are running).

Are you using replication on this database? If not, then this doesn't matter as these steps are not necessary.

When you login to SQL Server via Management Studio, you can explicitly select a database to connect to or use your default database. If your default database is unavailable, you will get a login failure.

This link details how to explicitly select a database to login to in SSMS. Basically, on the login window, click Options then select to Connection Properties tab. Under "Connect to Database:" type "master" and try logging in again.

If this allows you to regain access to the SQL Server, follow the steps in this SE answer to change the default DB for your administrators to "master" so that they have a better chance of being able to log in even when user databases are taken offline.