sp_defaultdb
Changes the default database for a login.
Syntax
sp_defaultdb [ @loginame = ] 'login' ,
[ @defdb = ] 'database'
Arguments
[@loginame =] 'login'
Is the login name. login is sysname, with no default. login can be an existing Microsoft® SQL Server™ login or a Microsoft Windows NT® user or group. If the Windows NT user or group does not exist in SQL Server, it is automatically added.
[@defdb =] 'database'
Is the name of the new default database. database is sysname, with no default. database must already exist.
Return Code Values
0 (success) or 1 (failure)
Remarks
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.
Permissions
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.
Examples
This example sets pubs as the default database for user Victoria.
EXEC sp_defaultdb 'Victoria', 'pubs'