Changes the attributes of a login account.
alter login login_name { [modify attribute_value_pair_list ] | [add auto activated roles role_name [, role_name_list ]] | [drop auto activated roles { ALL | role_name [, role_name_list ]}] | [drop attribute_name_list ] | [ with password caller_password modify password [immediately] new_loginName_password ] }
Parameter |
Parameter Value |
Description |
---|---|---|
login profile |
Valid values:
|
|
fullname |
name_value |
Full name of user who owns the login account. Adds a full name or modifies an existing name. Default is NULL. |
password expiration |
Valid range: 0 to 32767 days |
Password expiration interval. Default is 0, meaning the password never expires. |
min password length |
Valid range: 0 to 30. |
Minimum password length required. Default is 6. |
max failed attempts |
Valid range: -1 to 32767. |
Number of login attempts allowed, after which the login account is locked. -1 indicates the failed count is tracked but not locked. Default is 0, meaning the failed count is not tracked and the account is not locked due to failed login attempts. |
authenticate with |
Valid values: ASE, LDAP, PAM, KERBEROS, ANY |
Specifies the mechanism used for authenticating the login account. When ANY is used, the SAP ASE server checks for a defined external authentication mechanism. If one is defined, the SAP ASE server uses the defined mechanism., otherwise the ASE mechanism is used. If authenticate with authentication mechanism is not specified, ANY is used for the login account. |
default database |
default_database_name |
Specifies a database to be the default. Default is Master. |
default language |
default_language |
Specifies a language to be the default. Default is us_english |
login script |
login_script_name |
Specifies a valid stored procedure. Limited to 120 characters for a login script. |
exempt inactive lock |
Valid values: TRUE or FALSE. |
Specifies whether or not to exempt login accounts from being locked due to inactivity. Default is FALSE, which indicates account are not exempt. |
login profile – removes the login profile binding from the specified login account. If the login profile ignore parameter has been specified, the parameter is removed and existing default login profile is no longer ignored.
fullname – removes the name associated with the login account.
password expiration – removes any password expiration values.
min password length – removes any restrictions for a minimum password length.
max failed attempts – removes restrictions for the number of failed attempts allowed.
authenticate with – removes specifications for authentication mechanisms.
default database – removes specifications for a default database.
default languag – removes specifications for a default languages.
login script – removes specifications to apply a login script.
exempt inactive lock – removes specifications indicating whether or not to lock login accounts due to inactivity. Sets the default value of FALSE where login accounts are not exempt.
Specify immediately – the password changes immediately in the syslogins table, and users who are logged in get their passwords updated while they are still logged in.
Do not specify immediately – all users—with an exception to the caller—who are logged, in keep their old passwords until they reconnect.
alter login ravi modify login profile emp_lp
alter login users_1 modify login profile ignore
create login profile general_lp as default with default database master default language us_english track lastlogin true authenticate with ASE create login profile emp_lp with default database empdb autheticate with LDAP
The following binds the login profile emp_lp to the login account users_2. The default language and track lastlogin are not defined in login profile emp_lp but are defined in the default login profile. Therefore, the default language and track lastlogin values are applied from general_lp.
alter login users_22 modify login profile emp_lp
create login profile newEmployee_lp with login script "newEmp_script" create login profile default_lp as default with login script "def_script"
The following applies the login script newEmp_script to employee_new upon login.
create login employee_new with password myPasswd33 login profile "newEmployee_lp"
The login profile default_lp is applied upon login to existing accounts that do not have a login script specified through a login profile.
create login profile contractEmp_lp grant role access_role to contractEmp_lp alter login profile contractEmp_lp add auto activated roles access_role create login contractEmp_emp1 with password c_Emp43 login profile "contract_lp" create login contractEmp_emp2 with password c_Emp44 login profile "contract_lp" create login contractEmp_emp3 with password c_Emp44 login profile "contract_lp"
Precedence rules determine how login account attributes are applied when attributes are taken from different login profiles, or when values have been specified using sp_passwordpolicy.
For precedence rules, see Applying Login Profile and Password Policy Attributes in the Security Administration Guide.
create login, create login profile, alter login profile, drop login, drop login profile
For information about altering login accounts, see the Security Administration Guide.
lprofile_id, lprofile_name in Reference Manual: Building Blocks
sp_passwordpolicy, sp_displaylogin, sp_displayroles, sp_locklogin in Reference Manual: Procedures
ANSI SQL – Compliance level: Transact-SQL extension.
The permission checks for alter login differ based on your granular permissions settings.
Setting | Description |
---|---|
Enabled | With granular permissions enabled, you must have the manage any login privilege to alter login accounts in general. To modify a login account’s password, you must have the change password privilege or be the account owner. The account owner is allowed to modify the account’s full name. |
Disabled | With granular permissions disabled, you must have sso_role to alter login accounts in general. The account owner is allowed to modify the account’s password and full name. |
Values in event and extrainfo columns of sysaudits are:
Information | Values |
---|---|
Event | 138 |
Audit option | login_admin |
Command or access audited | alter login |
Information in extrainfo | Keywords contain:
|