I dropped a database from SQL Server, however it turns out that my login was set to use the dropped database as its default. I can connect to SQL Server Management Studio by using the 'options' button in the connection dialog and selecting 'master' as the database to connect to. However when ever I try to do anything in object explorer it tries to connect using my default database and fails.
Does anyone know how to set my default database without being able to log in?
What you can do is set your default database using the sp_defaultdb system stored procedure. Log in as you have done and then click the New Query button. After that simply run the sp_defaultdb command as follows:
Exec sp_defaultdb @loginame='login', @defdb='master'
To do it the GUI way, you need to go edit your login. One of its properties is the default database used for that login. You can find the list of logins under the Logins node under the Security node. Then select your login and right-click and pick Properties. Change the default database and your life will be better!
Note that someone with sysadmin privs needs to be able to login to do this or to run the query from the previous post.
If you don't have permissions to change your default DB you could manually select a different DB at the top of your quieries...
USE [SomeOtherDb]
SELECT 'I am now using a different DB'
Will work as long as you have permission to the other DB