totn SQL Server

SQL Server: ALTER LOGIN statement

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 statement 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

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.

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 techonthenet DISABLE;

This ALTER LOGIN example would disable the Login called techonthenet.

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 techonthenet ENABLE;

This ALTER LOGIN example would enable the Login called techonthenet.

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 techonthenet
WITH PASSWORD = 'bestsite'
UNLOCK;

This ALTER LOGIN example would unlock the Login called techonthenet and set the password to 'bestsite'.

Example - Rename a Login

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

For example:

ALTER LOGIN techonthenet
WITH NAME = checkyourmath;

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