alter login

Changes the attributes of a login account.

Syntax

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

Parameters

Examples

Usage

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.

See also:
  • 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

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for alter login differ based on your granular permissions settings.

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

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

138

Audit option

login_admin

Command or access audited

alter login

Information in extrainfo
Keywords contain:
  • MODIFY attribute_value_pair_list

  • DROP attribute_name_list

  • MODIFY PASSWORD

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

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

Related reference
alter login profile
create login
create login profile
drop login
drop login profile