alter login profile


Changes the attributes of a login profile.


alter login profile login_profile_name
	{  [as [ not ] default ]
	| [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] }


as [ not ] default

as default modifies the login profile to be the default login profile. as not default removes the default property of the specified login profile.


specifies the name of the login profile to be changed.


attribute values are changed 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 specified login profile. The attribute_value_ pair_list is an attribute name and a specified value. Specify one or more of the following attributes:


Parameter Value


default database


Specifies a database in Adaptive Server.

The default is master.

default language


Specifies a language.

The default is us_english

login script


Specifies a valid stored procedure. Limited to 120 characters for a login script.

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.

track lastlogin

Valid values: TRUE, FALSE.

Enables last login updates.

The default is TRUE, which is to update.

stale period

1 .. 32767 days.

Duration: D (days), W (weeks), M (months), Y (years)

Indicates the duration a login account is allowed to remain inactive before it is locked due to inactivity. The default is D (days).

add auto activated roles

specifies the previously granted non-password protected user defined roles that must be automatically activated on login. An error is generated if the role specified is not granted to the login. By default, user defined roles are not 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.

drop attribute_name_list

removes the following:

  • default database – removes the default database specification.

  • default language – removes thedefault languages specification.

  • login script– removes specifications to apply a login script.

  • authenticate with – removes specifications for authentication mechanisms that are associated with the account.

  • track last login – removes specifications that enable last login updates.

  • stale period – removes any restrictions that have been specified for the login account to remain inactive before it is locked.


Example 1

Configures eng_lp as the default login profile. If there is an existing default login profile, its default property is removed.

alter login profile eng_lp as default 

Example 2

Alters the login profile mgr_lp to automatically activate the previously granted program_role, product_role, and admin_role, roles on login if they are not password protected.

alter login profile mgr_lp add auto activated roles program _role, product_role, admin_role

Example 3

Alters the login profile mgr_lp to remove the automatic activation of the previously granted role admin_role on login.

alter login profile mgr_lp drop auto activated roles admin_role

Example 4

Alters the login profile mgr_lp to remove the login script attribute. Once removed, a login account associated with mgr_lp will use the values of a default login script, if one is defined. If one is not defined, the login script attribute be set to the default value, which is no login script with be invoked on login.

alter login profile mgr_lp drop login script 



ANSI SQL – Compliance level: Transact-SQL extension.


Only a system security officer can execute alter login profile.


Values in event and extrainfo columns of sysaudits are:


Audit option

Command or access audited

Information in extrainfo



alter login profile

Keywords contain:



  • DROP attribute_name_list

  • MODIFY attribute_value_pair_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, drop login, drop login profile

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

Functions lprofile_id, lprofile_name

System procedures sp_passwordpolicy, sp_displaylogin, sp_displayroles, sp_locklogin