Skip to content

sp-password-transact-sql.md

Latest commit

 

History

History
104 lines (80 loc) · 4.75 KB

sp-password-transact-sql.md

File metadata and controls

104 lines (80 loc) · 4.75 KB
title sp_password (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql-non-specified
ms.reviewer
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic language-reference
f1_keywords
sp_password
sp_password_TSQL
dev_langs
TSQL
helpviewer_keywords
sp_password
ms.assetid 0ecbec81-e637-44a9-a61e-11bf060ef084
caps.latest.revision 28
author BYHAM
ms.author rickbyh
manager jhubbard

sp_password (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]

Adds or changes a password for a [!INCLUDEmsCoName] [!INCLUDEssNoVersion] login.

Important

[!INCLUDEssNoteDepFutureAvoid] Use ALTER LOGIN instead.
Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through current version).

Topic link icon Transact-SQL Syntax Conventions

Syntax

  
sp_password [ [ @old = ] 'old_password' , ]  
     { [ @new =] 'new_password' }  
     [ , [ @loginame = ] 'login' ]

Arguments

[ @old= ] 'old_password'
Is the old password. old_password is sysname, with a default of NULL.

[ @new= ] 'new_password'
Is the new password. new_password is sysname, with no default. old_password must be specified if named parameters are not used.

Important

Do not use a NULL password. Use a strong password. For more information, see Strong Passwords.

[ @loginame= ] 'login'
Is the name of the login affected by the password change. login is sysname, with a default of NULL. login 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 additional options. For information on changing passwords, see ALTER LOGIN (Transact-SQL).

sp_password cannot 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. Changing 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 is the preferred method. The user that is executing this command must have CONTROL SERVER permission.

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

B. Changing 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 is the preferred method. User Victoria can execute this command without additional permissions. Other users require ALTER ANY LOGIN permission.

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

See Also

Security Stored Procedures (Transact-SQL)
ALTER LOGIN (Transact-SQL)
CREATE LOGIN (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_adduser (Transact-SQL)
sp_grantlogin (Transact-SQL)
sp_revokelogin (Transact-SQL)
System Stored Procedures (Transact-SQL)