Changes the default database for a login.
sp_defaultdb [@loginame =] 'login' , [@defdb =] 'database'
0 (success) or 1 (failure)
When a client connects with SQL Server, the default database defined for its login becomes the current database without an explicit USE statement. The default database can be defined when the login is added with sp_addlogin. When executing sp_addlogin the master database is the default database if a database is not specified.
After sp_defaultdb is executed, the login is connected to the new database the next time the user logs in. However, sp_defaultdb does not automatically give the login access to that database. The database owner (dbo) must give database access to the login through sp_grantdbaccess, or there must be a guest user specified in the database.
It is recommended that sp_defaultdb be used to change the default database for all logins other than members of the sysadmin fixed server role. This prevents users from inadvertently trying to use or create objects in the master database.
sp_defaultdb cannot be executed within a user-defined transaction.
Execute permissions default to the public role for users changing the default database for their own logins. Only members of the syadmin or securityadmin fixed server roles can execute sp_defaultdb for other logins.
This example sets pubs as the default database for user Victoria.
EXEC sp_defaultdb 'Victoria', 'pubs'
sp_addlogin | USE |
sp_droplogin | System Stored Procedures |
sp_grantdbaccess |