sp-defaultdb-transact-sql.md

title	sp_defaultdb (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/14/2017
ms.prod	sql
ms.prod_service	database-engine
ms.component	system-stored-procedures
ms.reviewer
ms.suite	sql
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	sp_defaultdb_TSQL sp_defaultdb
dev_langs	TSQL
helpviewer_keywords	sp_defaultdb
ms.assetid	663b859f-c6da-4942-95a6-60b93d05654e
caps.latest.revision	29
author	edmacauley
ms.author	edmaca
manager	craigg

sp_defaultdb (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]

Changes the default database for a [!INCLUDEmsCoName] [!INCLUDEssNoVersion] login.

Important

[!INCLUDEssNoteDepFutureAvoid] Use ALTER LOGIN instead.

Transact-SQL Syntax Conventions

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 [!INCLUDEssNoVersion] login or a Windows user or group. If a login for the Windows user or group does not exist in [!INCLUDEssNoVersion], 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

sp_defaultdb calls ALTER LOGIN. This statement supports additional options. For information about changing default database, see ALTER LOGIN (Transact-SQL).

sp_defaultdb cannot be executed within a user-defined transaction.

Permissions

Requires ALTER ANY LOGIN permission.

Examples

The following example sets [!INCLUDEssSampleDBnormal] as the default database for [!INCLUDEssNoVersion] login Victoria.

EXEC sp_defaultdb 'Victoria', 'AdventureWorks2012';  

See Also

Security Stored Procedures (Transact-SQL)
ALTER LOGIN (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_droplogin (Transact-SQL)
sp_grantdbaccess (Transact-SQL)
System Stored Procedures (Transact-SQL)