ALTER LOGIN command in SQL Server

The ALTER LOGIN command is used to modify the identity used to connect to SQL Server. You can use this command to change your password, it is required to change your password, disable, reactivate, unlock, rename your login .

Syntax of ALTER LOGIN

 ALTER LOGIN t en_dangnhap 
 { ENABLE | DISABLE 
 | WITH PASSWORD = 'matkhau' | matkhau_bam HASHED 
  [ OLD_PASSWORD = 'matkhau_cu' ] 
  | MUST_CHANGE 
  | UNLOCK 
  | DEFAULT_DATABASE = ten_cosodulieu 
  | DEFAULT_LANGUAGE = ten_ngonngu 
  | NAME = ten_dangnhap_moi 
  | CHECK_EXPIRATION = { ON | OFF } 
  | CHECK_POLICY = { ON | OFF } 
  | CREDENTIAL = ten_chungthuc 
  | NO CREDENTIAL 
 | ADD CREDENTIAL ten_chungthuc_moi 
 | DROP CREDE | DROP CREDE NTIAL ten_chungthuc}; 

Variable name or variable value

Username

Name of the login account.

ENABLE

Activate the login account.

DISABLE

Disable login account.

matkhau

New password is used for login account, authenticated by SQL Server Authentication.

matkhau_bam

The hash function value of the login password, authenticated by SQL Server Authentication.

matkhau_cu

Old password authenticated by SQL Server Authentication.

MUST_CHANGE

Used when forcing the password to change after the first login, is used after the ALTER LOGIN command.

UNLOCK

Unlock locked login account

ten_cosodulieu

Default database

ten_ngonngu

Default language for login account.

ten_dangnhap_moi

New name of the login account if you use the ALTER LOGIN command to change your username.

CHECK_EXPIRATION

The default is set to OFF. This option determines the password expiration policy. Must set CHECK_EXPIRATION = ON if using MUST_CHANGE.

ten_chungthuc

Authentication name of the login account.

NO CREDENTIAL

Delete any certificates mapped from the login account.

ADD CREDENTIAL

Add authentication to the login account.

DROP CREDENTIAL

Delete authentication for login account.

Note: See also the CREATE LOGIN and DROP LOGIN commands.

For example, change the password

 ALTER LOGIN q uantrimang 
 WITH PASSWOR D = 'congnghe'; 

This ALTER LOGIN command changes the Login with the name quantrimang and the password of this login account to congnghe.

For example, change the password and must change the password

 ALTER LOGIN quantrim ang 
 WITH PASSWORD = 'congnghe' MUST_CHANGE, 
 CHECK_EXPIRATION = ON; 

For example, this ALTER LOGIN command will change the Login named quantrimang, change the password to congnghe. Since selecting MUST_CHANGE and CHECK_EXPIRATION is ON, the password must be changed after logging in for the first time (after the ALTER LOGIN command). This will be like resetting a password to a temporary password.

Disable Login example

 ALTER LOGIN quantrimang DISABLE; 

This command will disable the Login named quantrimang.

For example, enable Login

 ALTER LOGIN quantrimang ENABLE; 

This command will activate the login account named quatrimang.

Example Login login

 ALTER LOGIN quant rimang 
 WITH PASSWORD = 'congnghe' 
 UNLOCK; 

The command in the above example will unlock the login account of quantrimang and set the password to be congnghe.

For example, change the name of Login

 ALTER LOGIN quantri mang 
 WITH NAME = 'hocsqls erver'; 

The above command will rename the login account quantrimang to hocsqlserver.

Last lesson: CREATE LOGIN command in SQL Server

The following article: DROP LOGIN command in SQL Server