sp_password (Transact-SQL)
Applies to: SQL Server
Adds or changes a password 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_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