ALTER LOGIN (Transact-SQL)
Changes the properties of a SQL Server login account.
Transact-SQL syntax conventions
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
In the following row, select the product name you're interested in, and only that product's information is displayed.
* SQL Server *
ALTER LOGIN login_name
{
<status_option>
| WITH <set_option> [ , ... ]
| <cryptographic_credential_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
<cryptographic_credentials_option> ::=
ADD CREDENTIAL credential_name
| DROP CREDENTIAL credential_name
Specifies the name of the SQL Server login that is being changed. Domain logins must be enclosed in brackets in the format [<domain>\<user>]
.
Enables or disables this login. Disabling a login doesn't affect the behavior of logins that are already connected. (Use the KILL
statement to terminate an existing connection.) Disabled logins retain their permissions and can still be impersonated.
Applies only to SQL Server logins. Specifies the password for the login that is being changed. Passwords are case-sensitive.
Applies to the HASHED keyword only. Specifies the hashed value of the password for the login that is being created.
Important
When a login (or a contained database user) connects and is authenticated, the connection caches identity information about the login. For a Windows Authentication login, this includes information about membership in Windows groups. The identity of the login remains authenticated as long as the connection is maintained. To force changes in the identity, such as a password reset or change in Windows group membership, the login must sign out from the authentication authority (Windows or SQL Server), and sign in again. A member of the sysadmin fixed server role or any login with the ALTER ANY CONNECTION
permission can use the KILL
command to end a connection and force a login to reconnect. SQL Server Management Studio can reuse connection information when opening multiple connections to Object Explorer and Query Editor windows. Close all connections to force reconnection.
Applies to SQL Server logins only. Specifies that the password entered after the PASSWORD argument is already hashed. If this option isn't selected, the password is hashed before being stored in the database. This option should only be used for login synchronization between two servers. Don't use the HASHED option to routinely change passwords.
Applies only to SQL Server logins. The current password of the login to which a new password will be assigned. Passwords are case-sensitive.
Applies only to SQL Server logins. If this option is included, SQL Server prompts for an updated password the first time the altered login is used.
Specifies a default database to be assigned to the login.
Specifies a default language to be assigned to the login. The default language for all SQL Database logins is English and can't be changed. The default language of the sa
login on SQL Server on Linux is English, but it can be changed.
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 can't contain a backslash character (\
).
Applies only to SQL Server logins. Specifies whether password expiration policy should be enforced on this login. The default value is 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.
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. A credential can't be mapped to the sa login.
Removes any existing mapping of the login to a server credential. For more information, see Credentials.
Applies only to SQL Server logins. Specifies that a login that is locked out should be unlocked.
Adds an Extensible Key Management (EKM) provider credential to the login. For more information, see Extensible Key Management (EKM).
Removes an Extensible Key Management (EKM) provider credential from the login. For more information, see [Extensible Key Management (EKM)] (../.. /relational-databases/security/encryption/extensible-key-management-ekm.md).
When CHECK_POLICY is set to ON, the HASHED argument can't be used.
When CHECK_POLICY is changed to ON, the following behavior occurs:
- 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 fails.
If CHECK_POLICY is set to OFF, CHECK_EXPIRATION can't be set to ON. An ALTER LOGIN statement that has this combination of options fail.
You can't use ALTER LOGIN with the DISABLE argument to deny access to a Windows group. For example, ALTER LOGIN [<domain>\<group>] DISABLE
returns the following error message:
"Msg 15151, Level 16, State 1, Line 1
Cannot alter the login '*Domain\Group*', because it doesn't exist or you don't have permission.
This is by design.
In SQL Database, login data required to authenticate a connection and server-level firewall rules are temporarily cached in each database. This cache is periodically refreshed. To force a refresh of the authentication cache and make sure that a database has the latest version of the logins table, execute DBCC FLUSHAUTHCACHE.
Requires ALTER ANY LOGIN permission.
If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.
If the login that is being changed is a member of the sysadmin fixed server role or 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.
The following example enables the login Mary5
.
ALTER LOGIN Mary5 ENABLE;
The following example changes the password of login Mary5
to a strong password.
ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';
If you're attempting to change the password of the login that you're currently logged in with, and you don't have the ALTER ANY LOGIN
permission you must specify the OLD_PASSWORD
option.
ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>' OLD_PASSWORD = '<oldWeakPasswordHere>';
The following example changes the name of login Mary5
to John2
.
ALTER LOGIN Mary5 WITH NAME = John2;
The following example maps the login John2
to the credential Custodian04
.
ALTER LOGIN John2 WITH CREDENTIAL = Custodian04;
The following example maps the login Mary5
to the EKM credential EKMProvider1
.
ALTER LOGIN Mary5 ADD CREDENTIAL EKMProvider1;
GO
To unlock a SQL Server login, execute the following statement, replacing ****
with the desired account password.
ALTER LOGIN [Mary5] WITH PASSWORD = '****' UNLOCK;
GO
To unlock a login without changing the password, turn off the check policy and then on again.
ALTER LOGIN [Mary5] WITH CHECK_POLICY = OFF;
ALTER LOGIN [Mary5] WITH CHECK_POLICY = ON;
GO
The following example changes the password of the TestUser
login to an already hashed value.
ALTER LOGIN TestUser WITH PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED;
GO
* SQL Database *
ALTER LOGIN login_name
{
<status_option>
| WITH <set_option> [ , .. .n ]
}
[;]
<status_option> ::=
ENABLE | DISABLE
<set_option> ::=
PASSWORD = 'password'
[
OLD_PASSWORD = 'oldpassword'
]
| NAME = login_name
Specifies the name of the SQL Server login that is being changed. Domain logins must be enclosed in brackets in the format [domain\user].
Enables or disables this login. Disabling a login doesn't affect the behavior of logins that are already connected. (Use the KILL
statement to terminate an existing connection.) Disabled logins retain their permissions and can still be impersonated.
Applies only to SQL Server logins. Specifies the password for the login that is being changed. Passwords are case-sensitive.
Continuously active connections to SQL Database require reauthorization (performed by the Database Engine) at least every 10 hours. The Database Engine attempts reauthorization using the originally submitted password and no user input is required. For performance reasons, when a password is reset in SQL Database, the connection isn't reauthenticated, even if the connection is reset due to connection pooling. This is different from the behavior of on-premises SQL Server. If the password has changed since the connection was initially authorized, the connection must be terminated and a new connection made using the new password. A user with the KILL DATABASE CONNECTION permission can explicitly terminate a connection to SQL Database by using the KILL command. For more information, see KILL.
Important
When a login (or a contained database user) connects and is authenticated, the connection caches identity information about the login. For a Windows Authentication login, this includes information about membership in Windows groups. The identity of the login remains authenticated as long as the connection is maintained. To force changes in the identity, such as a password reset or change in Windows group membership, the login must sign out from the authentication authority (Windows or SQL Server), and sign in again. A member of the sysadmin fixed server role or any login with the ALTER ANY CONNECTION permission can use the KILL command to end a connection and force a login to reconnect. SQL Server Management Studio can reuse connection information when opening multiple connections to Object Explorer and Query Editor windows. Close all connections to force reconnection.
Applies only to SQL Server logins. The current password of the login to which a new password will be assigned. Passwords are case-sensitive.
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 can't contain a backslash character (\).
In SQL Database, login data required to authenticate a connection and server-level firewall rules are temporarily cached in each database. This cache is periodically refreshed. To force a refresh of the authentication cache and make sure that a database has the latest version of the logins table, execute DBCC FLUSHAUTHCACHE.
Requires ALTER ANY LOGIN permission.
If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:
- Resetting the password without supplying the old password.
- Changing the login name.
- Enabling or disabling the login.
- Mapping the login to a different credential.
A principal can change the password for its own login.
These examples also include examples for using other SQL products. See which previous arguments are supported.
The following example enables the login Mary5
.
ALTER LOGIN Mary5 ENABLE;
The following example changes the password of login Mary5
to a strong password.
ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';
The following example changes the name of login Mary5
to John2
.
ALTER LOGIN Mary5 WITH NAME = John2;
The following example maps the login John2
to the credential Custodian04
.
ALTER LOGIN John2 WITH CREDENTIAL = Custodian04;
The following example maps the login Mary5
to the EKM credential EKMProvider1
.
Applies to: SQL Server 2008 (10.0.x) and later.
ALTER LOGIN Mary5 ADD CREDENTIAL EKMProvider1;
GO
The following example changes the password of the TestUser
login to an already hashed value.
Applies to: SQL Server 2008 (10.0.x) and later.
ALTER LOGIN TestUser WITH PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED;
GO
* SQL Managed Instance *
ALTER LOGIN login_name
{
<status_option>
| WITH <set_option> [ , ... ]
| <cryptographic_credential_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
<cryptographic_credentials_option> ::=
ADD CREDENTIAL credential_name
| DROP CREDENTIAL credential_name
-- Syntax for Azure SQL Managed Instance using Microsoft Entra logins
ALTER LOGIN login_name
{
<status_option>
| WITH <set_option> [ , .. .n ]
}
[;]
<status_option> ::=
ENABLE | DISABLE
<set_option> ::=
DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
Specifies the name of the SQL Server login that is being changed. Microsoft Entra logins must be specified as user@domain. For example, john.smith@contoso.com, or as the Microsoft Entra group or application name. For Microsoft Entra logins, the login_name must correspond to an existing Microsoft Entra login created in the master
database.
Enables or disables this login. Disabling a login doesn't affect the behavior of logins that are already connected. (Use the KILL
statement to terminate an existing connection.) Disabled logins retain their permissions and can still be impersonated.
Specifies a default database to be assigned to the login.
Specifies a default language to be assigned to the login. The default language for all SQL Database logins is English and can't be changed. The default language of the sa
login on SQL Server on Linux is English, but it can be changed.
Applies only to SQL Server logins. Specifies the password for the login that is being changed. Passwords are case-sensitive. Passwords also don't apply when used with external logins, like Microsoft Entra logins.
Continuously active connections to SQL Database require reauthorization (performed by the Database Engine) at least every 10 hours. The Database Engine attempts reauthorization using the originally submitted password and no user input is required. For performance reasons, when a password is reset in SQL Database, the connection isn't reauthenticated, even if the connection is reset due to connection pooling. This is different from the behavior of on-premises SQL Server. If the password has changed since the connection was initially authorized, the connection must be terminated and a new connection made using the new password. A user with the KILL DATABASE CONNECTION permission can explicitly terminate a connection to SQL Database by using the KILL command. For more information, see KILL.
Applies to the HASHED keyword only. Specifies the hashed value of the password for the login that is being created.
Applies to SQL Server logins only. Specifies that the password entered after the PASSWORD argument is already hashed. If this option isn't selected, the password is hashed before being stored in the database. This option should only be used for login synchronization between two servers. Don't use the HASHED option to routinely change passwords.
Applies only to SQL Server logins. The current password of the login to which a new password will be assigned. Passwords are case-sensitive.
Applies only to SQL Server logins. If this option is included, SQL Server prompts for an updated password the first time the altered login is used.
The new name of the login that is being renamed. If the login 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 can't contain a backslash character (\).
Applies only to SQL Server logins. Specifies whether password expiration policy should be enforced on this login. The default value is 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.
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. A credential can't be mapped to the sa login.
Removes any existing mapping of the login to a server credential. For more information, see Credentials.
Applies only to SQL Server logins. Specifies that a login that is locked out should be unlocked.
Adds an Extensible Key Management (EKM) provider credential to the login. For more information, see Extensible Key Management (EKM).
Removes an Extensible Key Management (EKM) provider credential from the login. For more information, see Extensible Key Management (EKM).
When CHECK_POLICY is set to ON, the HASHED argument can't be used.
When CHECK_POLICY is changed to ON, the following behavior occurs:
- 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 fails.
If CHECK_POLICY is set to OFF, CHECK_EXPIRATION can't be set to ON. An ALTER LOGIN statement that has this combination of options fail.
You can't use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. This is by design. For example, ALTER_LOGIN [domain\group] DISABLE returns the following error message:
"Msg 15151, Level 16, State 1, Line 1 "Cannot alter the login '*Domain\Group*', because it doesn't exist or you don't have permission."
In SQL Database, login data required to authenticate a connection and server-level firewall rules are temporarily cached in each database. This cache is periodically refreshed. To force a refresh of the authentication cache and make sure that a database has the latest version of the logins table, execute DBCC FLUSHAUTHCACHE.
Requires ALTER ANY LOGIN permission.
If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.
If the login that is being changed is a member of the sysadmin fixed server role or 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.
Only a SQL principal with sysadmin
privileges can execute an ALTER LOGIN command against a Microsoft Entra login.
These examples also include examples for using other SQL products. See which previous arguments are supported.
The following example enables the login Mary5
.
ALTER LOGIN Mary5 ENABLE;
The following example changes the password of login Mary5
to a strong password.
ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';
The following example changes the name of login Mary5
to John2
.
ALTER LOGIN Mary5 WITH NAME = John2;
The following example maps the login John2
to the credential Custodian04
.
ALTER LOGIN John2 WITH CREDENTIAL = Custodian04;
The following example maps the login Mary5
to the EKM credential EKMProvider1
.
Applies to: SQL Server 2008 (10.0.x) and later, and Azure SQL Managed Instance.
ALTER LOGIN Mary5 ADD CREDENTIAL EKMProvider1;
GO
To unlock a SQL Server login, execute the following statement, replacing ****
with the desired account password.
ALTER LOGIN [Mary5] WITH PASSWORD = '****' UNLOCK;
GO
To unlock a login without changing the password, turn off the check policy and then on again.
ALTER LOGIN [Mary5] WITH CHECK_POLICY = OFF;
ALTER LOGIN [Mary5] WITH CHECK_POLICY = ON;
GO
The following example changes the password of the TestUser
login to an already hashed value.
Applies to: SQL Server 2008 (10.0.x) and later, and Azure SQL Managed Instance.
ALTER LOGIN TestUser WITH PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED;
GO
The following example disables the login of a Microsoft Entra user, joe@contoso.com.
ALTER LOGIN [joe@contoso.com] DISABLE;
* Azure Synapse
Analytics *
ALTER LOGIN login_name
{
<status_option>
| WITH <set_option> [ , .. .n ]
}
[;]
<status_option> ::=
ENABLE | DISABLE
<set_option> ::=
PASSWORD = 'password'
[
OLD_PASSWORD = 'oldpassword'
]
| NAME = login_name
Specifies the name of the SQL Server login that is being changed. Domain logins must be enclosed in brackets in the format [domain\user].
Enables or disables this login. Disabling a login doesn't affect the behavior of logins that are already connected. (Use the KILL
statement to terminate an existing connection.) Disabled logins retain their permissions and can still be impersonated.
Applies only to SQL Server logins. Specifies the password for the login that is being changed. Passwords are case-sensitive.
Continuously active connections to SQL Database require reauthorization (performed by the Database Engine) at least every 10 hours. The Database Engine attempts reauthorization using the originally submitted password and no user input is required. For performance reasons, when a password is reset in SQL Database, the connection isn't reauthenticated, even if the connection is reset due to connection pooling. This is different from the behavior of on-premises SQL Server. If the password has changed since the connection was initially authorized, the connection must be terminated and a new connection made using the new password. A user with the KILL DATABASE CONNECTION permission can explicitly terminate a connection to SQL Database by using the KILL command. For more information, see KILL.
Important
When a login (or a contained database user) connects and is authenticated, the connection caches identity information about the login. For a Windows Authentication login, this includes information about membership in Windows groups. The identity of the login remains authenticated as long as the connection is maintained. To force changes in the identity, such as a password reset or change in Windows group membership, the login must sign out from the authentication authority (Windows or SQL Server), and sign in again. A member of the sysadmin fixed server role or any login with the ALTER ANY CONNECTION permission can use the KILL command to end a connection and force a login to reconnect. SQL Server Management Studio can reuse connection information when opening multiple connections to Object Explorer and Query Editor windows. Close all connections to force reconnection.
Applies only to SQL Server logins. The current password of the login to which a new password will be assigned. Passwords are case-sensitive.
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 can't contain a backslash character (\).
In SQL Database, login data required to authenticate a connection and server-level firewall rules are temporarily cached in each database. This cache is periodically refreshed. To force a refresh of the authentication cache and make sure that a database has the latest version of the logins table, execute DBCC FLUSHAUTHCACHE.
Requires ALTER ANY LOGIN permission.
If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:
- Resetting the password without supplying the old password.
- Changing the login name.
- Enabling or disabling the login.
- Mapping the login to a different credential.
A principal can change the password for its own login.
These examples also include examples for using other SQL products. See which previous arguments are supported.
The following example enables the login Mary5
.
ALTER LOGIN Mary5 ENABLE;
The following example changes the password of login Mary5
to a strong password.
ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';
The following example changes the name of login Mary5
to John2
.
ALTER LOGIN Mary5 WITH NAME = John2;
The following example maps the login John2
to the credential Custodian04
.
ALTER LOGIN John2 WITH CREDENTIAL = Custodian04;
The following example maps the login Mary5
to the EKM credential EKMProvider1
.
Applies to: SQL Server 2008 (10.0.x) and later.
ALTER LOGIN Mary5 ADD CREDENTIAL EKMProvider1;
GO
To unlock a SQL Server login, execute the following statement, replacing ****
with the desired account password.
ALTER LOGIN [Mary5] WITH PASSWORD = '****' UNLOCK;
GO
The following example changes the password of the TestUser
login to an already hashed value.
Applies to: SQL Server 2008 (10.0.x) and later.
ALTER LOGIN TestUser WITH PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED;
GO
* Analytics
Platform System (PDW) *
ALTER LOGIN login_name
{
<status_option>
| WITH <set_option> [ , ... ]
}
<status_option> ::= ENABLE | DISABLE
<set_option> ::=
PASSWORD = 'password'
[
OLD_PASSWORD = 'oldpassword'
| <password_option> [ <password_option> ]
]
| NAME = login_name
| CHECK_POLICY = { ON | OFF }
| CHECK_EXPIRATION = { ON | OFF }
<password_option> ::=
MUST_CHANGE | UNLOCK
Specifies the name of the SQL Server login that is being changed. Domain logins must be enclosed in brackets in the format [domain\user].
Enables or disables this login. Disabling a login doesn't affect the behavior of logins that are already connected. (Use the KILL
statement to terminate an existing connection.) Disabled logins retain their permissions and can still be impersonated.
Applies only to SQL Server logins. Specifies the password for the login that is being changed. Passwords are case-sensitive.
Important
When a login (or a contained database user) connects and is authenticated, the connection caches identity information about the login. For a Windows Authentication login, this includes information about membership in Windows groups. The identity of the login remains authenticated as long as the connection is maintained. To force changes in the identity, such as a password reset or change in Windows group membership, the login must sign out from the authentication authority (Windows or SQL Server), and sign in again. A member of the sysadmin fixed server role or any login with the ALTER ANY CONNECTION permission can use the KILL command to end a connection and force a login to reconnect. SQL Server Management Studio can reuse connection information when opening multiple connections to Object Explorer and Query Editor windows. Close all connections to force reconnection.
Applies only to SQL Server logins. The current password of the login to which a new password will be assigned. Passwords are case-sensitive.
Applies only to SQL Server logins. If this option is included, SQL Server prompts for an updated password the first time the altered login is used.
The new name of the login that is being renamed. If the login 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 can't contain a backslash character (\).
Applies only to SQL Server logins. Specifies whether password expiration policy should be enforced on this login. The default value is 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.
Applies only to SQL Server logins. Specifies that a login that is locked out should be unlocked.
When CHECK_POLICY is set to ON, the HASHED argument can't be used.
When CHECK_POLICY is changed to ON, the following behavior occurs:
- 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 fails.
If CHECK_POLICY is set to OFF, CHECK_EXPIRATION can't be set to ON. An ALTER LOGIN statement that has this combination of options fail.
You can't use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. This is by design. For example, ALTER_LOGIN [domain\group] DISABLE returns the following error message:
"Msg 15151, Level 16, State 1, Line 1 "Cannot alter the login '*Domain\Group*', because it doesn't exist or you don't have permission."
In SQL Database, login data required to authenticate a connection and server-level firewall rules are temporarily cached in each database. This cache is periodically refreshed. To force a refresh of the authentication cache and make sure that a database has the latest version of the logins table, execute DBCC FLUSHAUTHCACHE.
Requires ALTER ANY LOGIN permission.
If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.
If the login that is being changed is a member of the sysadmin fixed server role or 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.
These examples also include examples for using other SQL products. See which previous arguments are supported.
The following example enables the login Mary5
.
ALTER LOGIN Mary5 ENABLE;
The following example changes the password of login Mary5
to a strong password.
ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';
The following example changes the name of login Mary5
to John2
.
ALTER LOGIN Mary5 WITH NAME = John2;
The following example maps the login John2
to the credential Custodian04
.
ALTER LOGIN John2 WITH CREDENTIAL = Custodian04;
The following example maps the login Mary5
to the EKM credential EKMProvider1
.
Applies to: SQL Server 2008 (10.0.x) and later.
ALTER LOGIN Mary5 ADD CREDENTIAL EKMProvider1;
GO
To unlock a SQL Server login, execute the following statement, replacing ****
with the desired account password.
ALTER LOGIN [Mary5] WITH PASSWORD = '****' UNLOCK;
GO
To unlock a login without changing the password, turn off the check policy and then on again.
ALTER LOGIN [Mary5] WITH CHECK_POLICY = OFF;
ALTER LOGIN [Mary5] WITH CHECK_POLICY = ON;
GO
The following example changes the password of the TestUser
login to an already hashed value.
Applies to: SQL Server 2008 (10.0.x) and later.
ALTER LOGIN TestUser WITH PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED;
GO