Blog

This SQL Server tutorial explains how to use the SQL Server ALTER LOGIN statement with syntax and examples.

Description

The ALTER LOGIN statement modifies an identity used to connect to a SQL Server instance. You can use the ALTER LOGIN statement to change a password, force a password change, disable a login, enable a login, unlock a login, rename a login, etc.

Syntax

The syntax for the ALTER LOGIN statement in SQL Server is:

ALTER LOGIN login_name
{ ENABLE | DISABLE
| WITH PASSWORD = 'password' | hashed_password HASHED
         [ OLD_PASSWORD = 'old_password' ]
         | MUST_CHANGE
         | UNLOCK
       | DEFAULT_DATABASE = database_name
       | DEFAULT_LANGUAGE = language_name
       | NAME = new_login_name
       | CHECK_EXPIRATION = { ON | OFF }
       | CHECK_POLICY = { ON | OFF }
       | CREDENTIAL = credential_name
       | NO CREDENTIAL
| ADD CREDENTIAL new_credential_name
| DROP CREDENTIAL credential_name };

Parameters or Arguments

login_name

The Login name currently assigned to the Login.

ENABLE

Enables the Login.

DISABLE

Disables the Login.

password

The new password to assign to the Login that is authenticated using SQL Server authentication.

hashed_password

The hashed value of the password to assign to the Login using SQL Server authentication.

old_password

The old password using SQL Server authentication.

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.

database_name

The default database to assign to the Login.

language_name

The default language to assign to the Login.

new_login_name

The new name of the Login if you are using the ALTER LOGIN statements to rename a Login.

CHECK_EXPIRATION

By default, it is 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.

credential_name

The name of a credential to assign to the Login.

NO CREDENTIAL

Removes any mapped credentials from the Login.

ADD CREDENTIAL

Adds a credential to the Login

DROP CREDENTIAL.

Removes a credential from the Login.

Note

• See also the CREATE LOGIN statement and the DROP LOGIN statement.

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

Example – Disable a Login

Next, let’s look at how to disable a Login using the ALTER LOGIN statement in SQL Server (Transact-SQL).

For example:

ALTER LOGIN adglob DISABLE;

This ALTER LOGIN example would disable the Login called adglob.

Example – Enable a Login

Next, let’s look at how to enable a Login using the ALTER LOGIN statement in SQL Server (Transact-SQL).

For example:

ALTER LOGIN adglob ENABLE;

This ALTER LOGIN example would enable the Login called adglob.

Example – Unlock a Login

Next, let’s look at how to unlock a Login using the ALTER LOGIN statement in SQL Server (Transact-SQL).

For example:

ALTER LOGIN adglob
WITH PASSWORD = 'bestsite'
UNLOCK;

This ALTER LOGIN example would unlock the Login called adglob and set the password to ‘bestsite’.

Example – Rename a Login

Finally, let’s look at how to rename a Login using the ALTER LOGIN statements in SQL Server (Transact-SQL).

For example:

ALTER LOGIN adglob
WITH NAME = checkyourmath;

This ALTER LOGIN example would rename the Login called adglob to checkyourmath.