ALTER LOGIN (Transact-SQL)

Changes the properties of a SQL Server login account.

Topic link iconTransact-SQL Syntax Conventions

Syntax

ALTER LOGIN login_name 
    { 
    <status_option> 
    | WITH <set_option> [ ,... ] 
    } 

<status_option> ::=
        ENABLE | DISABLE

<set_option> ::=            
    PASSWORD = 'password' | hashed_password HASHED
    [ 
      OLD_PASSWORD = 'oldpassword' 
      | <password_option> [ <password_option> ] 
    ]
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
    | NAME = login_name
    | CHECK_POLICY = { ON | OFF }
    | CHECK_EXPIRATION = { ON | OFF }
    | CREDENTIAL = credential_name
    | NO CREDENTIAL
  
<password_option> ::= 
    MUST_CHANGE | UNLOCK

Arguments

  • login_name
    Specifies the name of the SQL Server login that is being changed.
  • ENABLE | DISABLE
    Enables or disables this login.
  • PASSWORD = 'password'
    Applies only to SQL Server logins. Specifies the password for the login that is being changed. Passwords are case-sensitive.
  • PASSWORD **=**hashed_password
    Applies to the HASHED keyword only. Specifies the hashed value for the password of the login that is being created.
  • HASHED
    Applies to SQL Server logins only. Specifies that the password entered after the PASSWORD argument is already hashed. If this option is not selected, the password is hashed before being stored in the database.

    Note

    This argument only works with hashes generated by SQL Server 2000 or later versions.

  • OLD_PASSWORD ='oldpassword'
    Applies only to SQL Server logins. The current password of the login to which a new password will be assigned. Passwords are case-sensitive.
  • MUST_CHANGE
    Applies only to SQL Server logins. If this option is included, SQL Server will prompt for an updated password the first time the altered login is used.
  • DEFAULT_DATABASE **=**database
    Specifies a default database to be assigned to the login.
  • DEFAULT_LANGUAGE **=**language
    Specifies a default language to be assigned to the login.
  • NAME = login_name
    The new name of the login that is being renamed. If this is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL Server. The new name of a SQL Server login cannot contain a backslash character (\).
  • CHECK_EXPIRATION = { ON | OFF }
    Applies only to SQL Server logins. Specifies whether password expiration policy should be enforced on this login. The default value is OFF.
  • CHECK_POLICY = { ON | OFF }
    Applies only to SQL Server logins. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.
  • CREDENTIAL = credential_name
    The name of a credential to be mapped to a SQL Server login. The credential must already exist in the server. For more information see Credentials.
  • NO CREDENTIAL
    Removes any existing mapping of the login to a server credential. For more information see Credentials.
  • UNLOCK
    Applies only to SQL Server logins. Specifies that a login that is locked out should be unlocked.

Remarks

When CHECK_POLICY is set to ON, the HASHED argument cannot be used.

When CHECK_POLICY is changed to ON, the following behavior occurs:

  • CHECK_EXPIRATION is also set to ON, unless it is explicitly set to OFF.
  • The password history is initialized with the value of the current password hash.

When CHECK_POLICY is changed to OFF, the following behavior occurs:

  • CHECK_EXPIRATION is also set to OFF.
  • The password history is cleared.
  • The value of lockout_time is reset.

If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.

If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. An ALTER LOGIN statement that has this combination of options will fail.

Important

CHECK_EXPIRATION and CHECK_POLICY are only enforced on Windows Server 2003 and later. For more information see Password Policy.

Important

A known issue in Windows Server 2003 might prevent the bad password count from resetting after the Account Lockout threshold has been reached. This could cause an immediate lockout on subsequent failed login attempts. You can manually reset the bad password count by briefly setting CHECK_POLICY = OFF, followed by CHECK_POLICY = ON. For more information about the Account Lockout threshold, see Microsoft Knowledge Base article 818078: Your User Account May Be Prematurely Locked Out.

You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. For example, ALTER_LOGIN [domain\group] DISABLE will return the following error message:

"Msg 15151, Level 16, State 1, Line 1

"Cannot alter the login 'Domain\Group', because it does not exist or you do not have permission."

This is by design.

Permissions

Requires ALTER ANY LOGIN permission.

If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.

If the login that is being altered is a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:

  • Resetting the password without supplying the old password.
  • Enabling MUST_CHANGE, CHECK_POLICY, or CHECK_EXPIRATION.
  • Changing the login name.
  • Enabling or disabling the login.
  • Mapping the login to a different credential.

A principal can change the password, default language, and default database for its own login.

Examples

A. Enabling a disabled login

The following example enables the login Mary5.

ALTER LOGIN Mary5 ENABLE;

B. Changing the password of a login

The following example changes the password of Mary5.

ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';

C. Changing the name of a login

The following example changes the name of login Mary5 to Joe2.

ALTER LOGIN Mary5 WITH NAME = Joe2;

D. Mapping a login to a credential

The following example maps the login Joe2 to the credential Custodian04.

ALTER LOGIN Joe2 WITH CREDENTIAL = Custodian04;

See Also

Reference

CREATE LOGIN (Transact-SQL)
DROP LOGIN (Transact-SQL)
CREATE CREDENTIAL (Transact-SQL)
EVENTDATA (Transact-SQL)

Other Resources

Credentials

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006

New content:
  • Added information about the HASHED argument.
  • Added information about using the DISABLE argument to prevent access to a Windows group.