This topic has not yet been rated - Rate this topic

sp_defaultdb (Transact-SQL)

SQL Server 2012

SQL Server 2005

Changes the default database for a Microsoft SQL Server login.

Important
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER LOGIN instead.

Topic link icon 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 SQL Server login or a Windows user or group. If a login for the Windows 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

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 AdventureWorks2012 as the default database for SQL Server login Victoria.

EXEC sp_defaultdb 'Victoria', 'AdventureWorks2012';

Reference

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)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD