How do I set the default database in Sql Server from code?

sql-server database sql-server-2000
Brian picture Brian · Sep 19, 2008 · Viewed 21.2k times · Source

I can't seem to figure out how to set the default database in Sql Server from code. This can be either .Net code or T-Sql (T-Sql would be nice since it would be easy to use in any language). I searched Google and could only find how to do it in Sql Server Management Studio.

Answer

Tim picture Tim · Sep 20, 2008

ALTER LOGIN should be used for SQL Server 2005 or later:

http://technet.microsoft.com/en-us/library/ms189828.aspx

ALTER LOGIN <login_name> WITH DEFAULT_DATABASE = <default_database>

sp_defaultdb eventually will be removed from SQL Server:

http://technet.microsoft.com/en-us/library/ms181738.aspx

Related questions

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to replace a string in a SQL Server Table Column

I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change. In the path column, I have many records and I need to change just a portion of the path, but …

Read More