How can I change my default database in SQL Server without using MS SQL Server Management Studio?

sql-server sql-server-2005 ssms default-database
Martin Brown picture Martin Brown · Dec 12, 2008 · Viewed 249.6k times · Source

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?

Answer

Martin Brown picture Martin Brown · Dec 12, 2008

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'

Related questions

How do you specify a different port number in SQL Management Studio?

I am trying to connect to a Microsoft SQL 2005 server which is not on port 1433. How do I indicate a different port number when connecting to the server using SQL Management Studio?

Read More
Convert SSMS .rpt output file to .txt/.csv

I want to export my big SSMS (SQL Server Management Studio) query result (2.5m lines, 9 fields) as .csv or comma-delimited .txt (with headings). (MS SQL Server 2005 Management Studio.) So that I can then either read it line-by-line into VBA program (…

Read More
Query runs fast in Query Analyzer but slow in C# application

Possible Duplicate: sql runs fast in ssms slow in asp.net Using SQL Server 2005, there is a sql query on a few big tables and it times out in the program. However, when I run it in Query Analyzer, it …

Read More