Changes a user's default database.
sp_defaultdb login_id, defdb
where
A default database can be set by using either sp_defaultdb or sp_addlogin when the user's login ID is initially added to SQL Server.
If no default database has been specified, users are connected to the master database.
After sp_defaultdb is executed, the user is connected to the new defdb the next time he or she logs in. However, this system stored procedure does not automatically give the user access to that database. The database owner must give database access to the user through sp_adduser or sp_addalias, or there must be a guest user specified in the database's sysusers table. A user who does not have access to the database by any of these means will be connected to the master database and an error message will be displayed.
If a user's default database is dropped, the user is connected to master on the next login and an error message is displayed.
The system administrator can change anyone's default database by using sp_defaultdb. Users can change only their own default database.
This example sets pubs as the default database for Victoria.
sp_defaultdb Victoria, pubs
Execute permission defaults to public for a user's login ID only. Execute permission defaults to the system administrator for any login ID.
master.dbo.sysdatabases, master.dbo.syslogins
| sp_addlogin | sp_password |
| sp_droplogin | USE |