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, whenever 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 using object explorer?
Alternative to sp_defaultdb (which will be removed in a future version of Microsoft SQL Server) could be ALTER LOGIN:
ALTER LOGIN [my_user_name] WITH DEFAULT_DATABASE = [new_default_database]
Note: user and database names are provided without quotes (unlike the sp_defaultdb solution). Brackets are needed if name had special chars (most common example will be domain user which is domain\username and won't work without brackets):
ALTER LOGIN me WITH DEFAULT_DATABASE = my_database
but
ALTER LOGIN [EVILCORP\j.smith28] WITH DEFAULT_DATABASE = [prod\v-45]