Blog

SQL Server: Change a password in SQL Server

change the passwords

Question: How do I change the passwords 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 change a passwords 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 adglob
WITH PASSWORD = 'bestsite';

This ALTER LOGIN example would alter the Login called adglob 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 adglob
WITH PASSWORD = 'bestsite' MUST_CHANGE, 
CHECK_EXPIRATION = ON;

This ALTER LOGIN example would alter the Login called adglob 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.

Leave a Reply