sp_password
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.

[@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 a member of the sysadmin fixed server role.

Return Code Values
 0  (success) or  1  (failure)

...

Permissions
Execute permissions default to the public role for a user changing the password for his or her own login. Only members of the sysadmin role can change the password for another user's login.