I love playing around with test instances. Recently I've been doing a lot of preparation for some forthcoming blog posts and when trying to log into SSMS this morning I received the following error message:
Unfortunately as it's one of my test instances I'm the only person using it, which also means I'm the only person responsible for creating this problem but we don't have a blame culture around here so let's get on with fixing it and I'll have a lessons learned with myself later over a glass of something.
This error isn't to do with my login as such, it's still there with sysadmin role membership so I don't have to do anything too drastic like restarting SQL Server with the -m or -f startup parameters and recreate it. The error message is telling me that my logins default database cannot be opened, which is more than likely because I've deleted it.
To resolve the issue I just need to connect to a different database but thing is it's not immediately obvious how to do that (and I don't have another login available to simply change my default database). What I need to do is click the Options >> button on the bottom right of the Connect to Server dialog box:
If I then go to the Connection Properties tab I can then use the Connect to database option and browse the server for another database to connect to:
But there's a problem, in order to browse the server I need to connect to it and as I can't do that right now I'm going to run into the same problem again:
Which sucks...
Fortunately I don't have to browse the server and choose a database to connect to. I can just type into the connect to database box and put in a valid database name such as master like in the box below:
Now I've put in a valid database I can successfully connect to my instance in Management Studio and the first thing that I will do is change my logins default database to master with the following T-SQL and the issue won't re-occur:
ALTER LOGIN [DESKTOP-MFKSHR7\David] WITH DEFAULT_DATABASE = master;
No comments:
Post a Comment