sp_password System Stored Procedure

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

Syntax

sp_password old, new [, login_id]

where

old

Is the user's old password, which is encrypted in master.dbo.syslogins. The default password for all users is NULL. The system administrator can specify NULL for this parameter no matter what the user's old password was. However, if the system administrator gives any value besides NULL, SQL Server checks the value against master.dbo.syslogins and rejects it if it is wrong.

new

Is the user's new password.

login_id

Is an option that only the system administrator can use to change any user's password.

Remarks

Any user can use sp_password to change his or her own password. Only the system administrator can change someone else's password (using the login_id parameter).

The old parameter is checked against the existing password for the user login_id (unless the user executing the procedure is the system administrator and the old parameter is NULL) and must match for the password to be changed. Passwords are encrypted in master.dbo.syslogins.password.

If front-end programs require the same password on remote servers as on the local server, a user must change his or her password on all the remote servers before changing the local password. The sp_password procedure should be executed as a call to a remote stored procedure on each remote server.

Permission

Execute permission defaults to the public group. Each member of public has permission to change only his or her own password. The system administrator can change any user's password.

Table Used

master.dbo.syslogins

Examples

A.    SA Can Change Password Without Knowing the Former Password

In this example, the system administrator has changed Victoria's password to ok. Notice that NULL is not enclosed in quotation marks.

sp_password NULL, ok, Victoria

B.    A User Changes Her Password

In this example, the user Victoria changes her password from ok to coffee.

sp_password ok, coffee

See Also