Upgrade your Internet Experience
Microsoft.com
Welcome Sign in
SQL Server Developer Center
Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2008
Database Engine
Technical Reference
 sp_password (Transact-SQL)

  Switch on low bandwidth view
Community Content
In this section
Statistics Annotations (0)
Other versions are also available for the following:
SQL Server 2008 Books Online (June 2009)
sp_password (Transact-SQL)

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

ms174428.note(en-us,SQL.100).gifImportant:
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

sp_password [ [ @old = ] 'old_password' , ]
     { [ @new =] 'new_password' }
     [ , [ @loginame = ] 'login' ]
[ @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.

ms174428.security(en-us,SQL.100).gifSecurity Note:
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.

0 (success) or 1 (failure)

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.

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.

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
Tags What's this?: good (x) Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Does *NOT* require ALTER ANY LOGIN and CONTROL SERVER      Booji Boy   |   Edit   |   Show History
The documentation is wrong. Just like in SQL Server 2000, in SQL Server 2008 sp_password still only requires membership in the db_accessadmin & db_securityadmin database roles and membership in the securityadmin server role to allow a user to change passwords w/o knowing the old password.
Tags What's this?: Add a tag
Flag as ContentBug
© 2009 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker