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

Question

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 1

from: http://doc.ddart.net/mssql/sql70/sp_da-di_6.htm

sp_defaultdb [@loginame =] 'login' , [@defdb =] 'database'

Answer 2

If you're trying to change which database you are using after you are logged in, you can use the USE command. E.g. USE Northwind.

http://doc.ddart.net/mssql/sql70/ua-uz_7.htm

Answer 3

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

Answer 4

Thanks Stephen.

As a note, if you are using Windows Authentication, the @loginname is YourDomain\YourLogin (probably obvious to everybody else, but took me a couple tries :).

sp_defaultdb @loginame='YourDomain\YourLogin' , @defdb='YourDatabase'