"Cannot open user default database. Login failed" after altering MDF and LDF locations and bringing DB offline 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.
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.