totn SQL Server

SQL Server: Change a password in SQL Server

Question: How do I change the password for a user/login in SQL Server?

Answer: In SQL Server, the password is associated with the SQL Server Login. The Login is then mapped to the database user. So to change a password in SQL Server, you need to execute the ALTER LOGIN statement.

Syntax

The syntax for changing a password in SQL Server (Transact-SQL) using the ALTER LOGIN statement is:

ALTER LOGIN login_name
WITH PASSWORD = 'password' | hashed_password HASHED
         [ OLD_PASSWORD = 'old_password' ]
         | MUST_CHANGE
         | UNLOCK
[ CHECK_EXPIRATION = ON ];

Parameters or Arguments

login_name
The Login whose password you wish to change. This Login will the one associated with the database user whose password you wish to change.
password
The new password to assign.
hashed_password
The new hashed value of the password to assign to the Login.
old_password
The old password.
MUST_CHANGE
It is used when you want to force the password to be changed the first time that the Login is used after the ALTER LOGIN statement.
UNLOCK
It will unlock a Login that has been locked out.
CHECK_EXPIRATION
It is by default set to OFF. This option determines whether password expiration policy is enforced. You must specifiy CHECK_EXPIRATION = ON when you use the MUST_CHANGE option.

Example - Change Password

Let's look at how to change a password using the ALTER LOGIN statement in SQL Server (Transact-SQL).

For example:

ALTER LOGIN techonthenet
WITH PASSWORD = 'bestsite';

This ALTER LOGIN example would alter the Login called techonthenet and change the password of this login to 'bestsite'.

Example - Change Password and Force Change

Let's look at how to change a password and force the password to be changed after the first login using the ALTER LOGIN statement in SQL Server (Transact-SQL).

For example:

ALTER LOGIN techonthenet
WITH PASSWORD = 'bestsite' MUST_CHANGE, 
CHECK_EXPIRATION = ON;

This ALTER LOGIN example would alter the Login called techonthenet and change the password of this login to 'bestsite'. But because we have specified the MUST CHANGE option and set the CHECK_EXPIRATION to ON, the password will have to be changed again in SQL Server after the first login (following the ALTER LOGIN statement). So in effect, it is like resetting a password to a temporary password for a Login.

Share on: