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.
Add a comment
|
4 Answers
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:
3 Comments
Brian
Thanks, but I'm using Sql Server 2000 and the ALTER LOGIN command doesn't appear to work in it: Incorrect syntax near 'LOGIN'.
joedotnot
I tried single 'quotes' and round (brackets) and was getting syntax error too. Then i changed to square brackets and it worked! ALTER LOGIN [login_name] WITH DEFAULT_DATABASE = [default_database]
NateJ
This should be the accepted answer for SQL Server in general (2005-up); but the OP stated SQL 2000 (but didn't tag it correctly), so in that case it's not, and the accepted answer is correct.
from: http://doc.ddart.net/mssql/sql70/sp_da-di_6.htm
sp_defaultdb [@loginame =] 'login' , [@defdb =] 'database'
1 Comment
NateJ
Note that this is mainly appropriate for older SQL versions; the answer below (stackoverflow.com/a/106015/112764) is better for 2005 & up.
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'
Comments
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.
