Today I was working with a database using LocalDB server and I had set this specific database named CBA as the default one for my user LENIEL-PC\Leniel. Setting a default database for the user is useful because when you login using SQL Server Management Studio [ SSMS ] you go directly to that database, that is, when running queries that’s the database used and not the master one. This is life saver.
I wanted to restore a backup file .bak I got from the production server using the Restore Database… command in SSMS. This is to test locally but first I had to drop my local database copy. I did drop my local copy while I was logged in to that database. I Selected the database in Object Explorer, right clicked it and selected Delete. Then checked the Close existing connections (bottom of the window), clicked OK and then the deletion succeeded. Pretty good… no.
What happened? The next time I tried to login again in SSMS this was what I got:
Cannot open database requested by login. The login failed.
Login failed for user LENIEL-PC\Leniel (Microsoft SQL Server, Error: 4060)
Pretty obvious! I deleted the default database I had set to my user LENIEL-PC\Leniel. Now what? How can I login to the database server and change the default database? Good question…
This “simple” error was such a pain to fix…I tried lots of things like using SqlLocalDB.exe utility to stop and delete that specific LocalDB instance named v11.0. I would create it again afterwards if deleting it worked at least. This was the message logged in Windows Event logs when I tried to delete the instance:
Windows API call LogSqlDiagRec returned error code: 0. Windows system error message is: The operation completed successfully.
Reported at line: 3791. [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "CBA" requested by the login. The login failed.
As you see the problem was the same. It was barking about the deleted database. Damn it!
I also tried the login with the sa account but guess what… the sa account was disabled for that LocalDB instance. Even if it was enabled I would not be able to login because I didn’t know the password for sa.
Googling more I managed to find this post by Pinal Dave:
So guess what: the solution is ridiculously easy once you know what to do of course…
Click that Options >> button in Figure 1. Now select the Connection Properties tab.
I had to type master in Connect to database field since I did not have it in the list of available databases.
Now click connect and you’re done.
Great. Thanks God I was able to login and access the database server.
What’s left?
Reset my user Default database to master so that I can login again directly. By the way, the field Default database was empty since I had dropped the database!
Doing so there’s no need to set the Connect to database option anymore.
Sometimes things are so easy but they are hidden somewhere we just can’t figure it out at first.
Hope it helps.
