sp_password (T-SQL)

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)

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.

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. Only members of the sysadmin role can change the password for another user’s login.

Examples

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

This example changes the password for the login Victoria to ok.

EXEC sp_password NULL, 'ok', 'Victoria'

B. Change a password

This example changes the password for the login Victoria from ok to coffee.

EXEC sp_password 'ok', 'coffee'

See Also