sp_defaultdb (Transact-SQL)
Applies to: SQL Server
Changes the default database for a SQL Server login.
Important
This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER LOGIN instead.
Transact-SQL syntax conventions
Syntax
sp_defaultdb
[ @loginame = ] N'loginame'
, [ @defdb = ] N'defdb'
[ ; ]
Arguments
[ @loginame = ] N'loginame'
The login name. @loginame is sysname, with no default. @loginame can be an existing SQL Server login or a Windows user or group. If a login for the Windows user or group doesn't exist in SQL Server, it's automatically added.
[ @defdb = ] N'defdb'
The name of the new default database. @defdb is sysname, with no default. @defdb must already exist.
Return code values
0
(success) or 1
(failure).
Remarks
sp_defaultdb
calls ALTER LOGIN
, which supports extra options. For information about changing default database, see ALTER LOGIN.
sp_defaultdb
can't be executed within a user-defined transaction.
Permissions
Requires ALTER ANY LOGIN
permission.
Examples
The following example sets AdventureWorks2022
as the default database for SQL Server login Victoria
.
EXEC sp_defaultdb 'Victoria', 'AdventureWorks2022';