alter login

Description

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

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

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

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 
"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"

Usage

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.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

Auditing

Values in event and extrainfo columns of sysaudits are:

See also