Skip to content

Latest commit

 

History

History
105 lines (74 loc) · 3.7 KB

sp-password-transact-sql.md

File metadata and controls

105 lines (74 loc) · 3.7 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_password (Transact-SQL)
sp_password adds or changes a password for a SQL Server login.
VanMSFT
vanto
randolphwest
08/21/2024
sql
system-objects
reference
sp_password
sp_password_TSQL
sp_password
TSQL

sp_password (Transact-SQL)

[!INCLUDE SQL Server]

Adds or changes a password for a [!INCLUDE ssNoVersion] login.

Important

[!INCLUDE ssNoteDepFutureAvoid] Use ALTER LOGIN instead.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_password
    [ [ @old = ] N'old' ]
    , [ @new = ] N'new'
    [ , [ @loginame = ] N'loginame' ]
[ ; ]

Arguments

[ @old = ] N'old'

The old password. @old is sysname, with a default of NULL.

[ @new = ] N'new'

The new password. @new is sysname, with no default. @old must be specified if named parameters aren't used.

Important

Don't use a NULL password. Use a strong password. For more information, see Strong Passwords.

[ @loginame = ] N'loginame'

The name of the login affected by the password change. @loginame is sysname, with a default of NULL. @loginame must already exist and can be specified only by members of the sysadmin or securityadmin fixed server roles.

Return code values

0 (success) or 1 (failure).

Remarks

sp_password calls ALTER LOGIN. This statement supports more options. For information on changing passwords, see ALTER LOGIN.

sp_password can't be executed within a user-defined transaction.

Permissions

Requires ALTER ANY LOGIN permission. Also requires CONTROL SERVER permission to reset a password without supplying the old password, or if the login that is being changed has CONTROL SERVER permission.

A principal can change its own password.

Examples

A. Change the password of a login without knowing the old password

The following example shows how to use ALTER LOGIN to change the password for the login Victoria to B3r1000d#2-36. This method is preferred. The user that is executing this command must have CONTROL SERVER permission.

ALTER LOGIN Victoria
    WITH PASSWORD = 'B3r1000d#2-36';
GO

B. Change a password

The following example shows how to use ALTER LOGIN to change the password for the login Victoria from B3r1000d#2-36 to V1cteAmanti55imE. This method is preferred. User Victoria can execute this command without extra permissions. Other users require ALTER ANY LOGIN permission.

ALTER LOGIN Victoria
    WITH PASSWORD = 'V1cteAmanti55imE'
    OLD_PASSWORD = 'B3r1000d#2-36';
GO

Related content