alter login


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 ] }



specifies the name of the login account to be changed.


changes attribute values to the new values specified if the attributes exist. If the attributes do not exist, the specified list of attributes and corresponding values are added to the login account. The attribute_value_ pair_list is an attribute name and a specified value. Specify one or more from the following:


Parameter Value


login profile

Valid values:

  • login_profile_name

  • ignore

  • default

  • login_profile_name binds the specified login profile to the specified login account. If a login profile binding already exist, it will be replaced with the specified login profile.

  • ignore eliminates any login profile binding. If a login profile binding exist, it will be removed. A default login profile will not be applicable and attributes will be applied as they were prior to release 15.7.

  • default default removes an existing login profile binding and associates the default login profile with the login account.

    If login profile login_profile_name is not specified and a default login profile exists, then the default login profile will be associated with the login account.



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


Specifies the mechanism used for authenticating the login account.

When ANY is used, Adaptive Server checks for a defined external authentication mechanism. If one is defined, Adaptive Server uses the defined mechanism., otherwise the ASE mechanism is used.

If authenticate with authentication mechanism is not specified, ANY will be used for the login account.

default database


Specifies a database to be the default.

Default is Master.

default language


Specifies a language to be the default.

Default is us_english

login script


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.

add auto activated roles

specifies the previously granted, non-password protected user defined roles that must be automatically activated on login.

drop auto activated roles

specifies the previously granted user defined roles must not be automatically activated on login. ALL specifies all granted user defined roles.


drops specified attributes from the login account. Specify one or more of the following attributes to be dropped:


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.


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 language

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.

with password caller_password modify new_loginName_password

changes the login password to the new specified password.


specifies whether a password immediately takes effect on users who are logged in. If you:

  • 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.


Example 1

Binds the login profile emp_lp to the login account ravi.

alter login ravi modify login profile emp_lp

Example 2

When ignore is specified, all login profiles are ignored, whether it is a login profile that has been bound to the users_1 login account or a defined default login profile.

alter login users_1 modify login profile ignore

Example 3

Creates two login profiles; the first is general_lp which is a default login profile and the second is a login profile name emp_lp which is defined for a specific group of employees. After the login profiles are created, attributes from both login profiles are applied to a login account. See “Applying login profile and password policy attributes” in the Security Administration Guide for information about the order in which attributes are applied.

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

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

Example 4

Creates two login profiles; the first is newEmployee_lp for new employees and the second is default_lp for existing employees.

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

The login profile default_lp is applied upon login to existing accounts that do not have a login script specified through a login profile.

Example 5

Shows how to enforce different roles that are granted and automatically activated for contract employees and full time employees:

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 
create login contractEmp_emp2 with password c_Emp44 login profile
create login contractEmp_emp3 with password c_Emp44 login profile


Precedence rules determine how login account attributes will be 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.


ANSI SQL – Compliance level: Transact-SQL extension.


You must have sso_role privileges to alter login accounts. The exception to sso_role is only when you change your own password and full name.


Values in event and extrainfo columns of sysaudits are:


Audit option

Command or access audited

Information in extrainfo



alter login

Keywords contain:

  • MODIFY attribute_value_pair_list

  • DROP attribute_name_list


  • ADD AUTO ACTIVATED ROLES role1 [role2][ ... [ roleN]... ]]

  • DROP AUTO ACTIVATED ROLES {ALL | role1 [, role2 [... [, roleN] ... ] ] }

See also

Commands create login, create login profile, alter login profile, drop login, drop login profile

Documents For information about altering login accounts, see the Security Administration Guide.

Functions lprofile_id, lprofile_name

System procedures sp_passwordpolicy, sp_displaylogin, sp_displayroles, sp_locklogin