Export (0) Print
Expand All
Expand Minimize

sp_password

SQL Server 2000

  New Information - SQL Server 2000 SP3.

Adds or changes a password for a Microsoft® SQL Server™ login.

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.

Security Note  Do not use a NULL password. Use a strong password. For more information, see Security Rules.

[@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 only be specified by members of the sysadmin or securityadmin fixed server roles.

Return Code Values

0 (success) or 1 (failure)

Remarks

SQL Server passwords can contain from 1 to 128 characters, including any letters, symbols, and numbers.

The new password is updated and stored in an encrypted form so that no user, not even system administrators, can view the password.

When members of the sysadmin or securityadmin fixed server role reset their own password using sp_password with all three arguments, the audit record will reflect that they are changing someone else's password.

sp_password cannot be used with Microsoft Windows NT® security accounts. Users connecting to SQL Server through their Windows NT network account are authenticated by Windows NT; therefore their passwords can be changed only in Windows NT.

sp_password cannot be executed within a user-defined transaction.

Permissions

Execute permissions default to the public role for a user changing the password for his or her own login. Members of the securityadmin and sysadmin fixed server roles can change the password for another user's login.

Examples
A. Change the password of a login without a former password

This example changes the password for the login Victoria from a NULL password to "B3r12-36".

EXEC sp_password NULL, 'B3r12-36', 'Victoria'
B. Change a password

This example changes the password for the login Victoria from "B3r12-36" to "Xj7-IpSca".

EXEC sp_password 'B3r12-36', 'Xj7-IpSca'

See Also

sp_addlogin

sp_adduser

sp_grantlogin

sp_revokelogin

System Stored Procedures

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft