ALTER LOGIN POLICY statement

ALTER LOGIN POLICY policy-name policy-options

policy options :
policy-option [ policy-option ... ]

policy-option :
policy-option-name = policy-option-value

policy-option-value : 
{ UNLIMITED 
| DEFAULT 
| legal-option-value }

policy-name The name of the login policy. Specify root to modify the root login policy.
policy-option-name The name of the policy option.
policy-option-value The value assigned to the login policy option. If you specify UNLIMITED, no limits are used. If you specify DEFAULT, the default limits are used.

When a login policy is altered, changes are immediately applied to all users.

If you do not specify a policy option, values for the login policy are taken from the root login policy.

All new databases include a root login policy. You can modify the root login policy values, but you cannot delete the policy. An overview of the default options for the root login policy is provided in the table below.

Policy-option-name	Description	Default value	Applies to
password_life_time	The maximum number of days before a password must be changed.	Unlimited	All users, including those with DBA authority.
password_grace_time	The number of days before the password expires during which login is allowed, but the default post_login procedure issues warnings.	0	All users, including those with DBA authority.
password_expiry_on_next_login	If the value for this option is ON, the user's password expires after the next login.	OFF	All users, including those with DBA authority.
locked	If the value for this option is ON, users are not allowed to establish new connections. Users with DBA authority cannot be locked. The reason_locked column of the sa_get_user_status system procedure returns a string generated by the database server that shows why a user is locked.	OFF	Users without DBA authority.
max_connections	The maximum number of concurrent connections allowed for a user.	Unlimited	Users without DBA authority.
max_failed_login_attempts	The maximum number of failed attempts since the last successful attempt to log in before the user is locked.	Unlimited	All users, including those with DBA authority. DBA user accounts are unlocked after one minute has passed since the most recent failed login attempt.
max_days_since_login	The maximum number of days that can elapse between two successive logins by the same user.	Unlimited	Users without DBA authority.
max_non_dba_connections	The maximum number of concurrent connections that users without DBA authority can make. This option is only supported in the root login policy.	Unlimited	Users without DBA authority and only to the root login policy.

DBA authority

None.

SQL/2008 Vendor extension.

The following example alters the Test1 login policy. This example changes the locked and max_connections options. The locked value indicates that users with the policy cannot establish new connections and the max_connections value limits the number of concurrent connections that are allowed.

ALTER LOGIN POLICY Test1 
locked=ON
max_connections=5;

ALTER LOGIN POLICY statement

Syntax

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Examples