sp_passwordpolicy includes these changes:
The set and clear commands in sp_passwordpolicy are now audited, through audit event 115, “Password Administration.”
Additional syntax:
sp_passwordpolicy “enable last login updates”, “allow password downgrade” “regenerate keypair”, “expire login passwords”, “[login_name | wildcard]” “expire role passwords”, “[role_name | wildcard]” “expire stale login passwords”, “datetime” “expire stale role passwords”, “datetime” "maximum failed logins", -1
Where:
login_name – is the name of the login whose password you are expiring.
role_name – is the name of role whose password you are expiring.
wildcard – is a wildcard character used for search purposes.
enable last login updates – is used to enable or disable code in Adaptive Server authentication that records the timestamp when each login occurs.The first parameter “set” sets the value of this attribute.The parameter “list” displays the current value of the attribute, and the parameter “clear” deletes the row from sysattributes. On upgrade or new installation, this attribute does not exist in sysattributes. The login timestamp occurs when the attribute row does not exist or has a value of 1. The login timestamp is not maintained if the attribute value is 0:
sp_passwordpolicy "set", "enable last login updates", 1 or 0
sp_passwordpolicy "list", "enable last login updates"
sp_passwordpolicy "clear", "enable last login updates"
allow password downgrade – ends the password downgrade period. During the password downgrade period, passwords are stored in syslogins in both old and new encodings to allow user passwords to retained if the server is downgraded, for example, to Adaptive Server 15.0.2:
sp_passwordpolicy 'set' 'allow password downgrade', 0
regenerate keypair – generates the asymmetric key pairs to be used for network login password encryption.There is no catalog update for this option; the actions occur only in memory fields.
expire state login passwords – login passwords expire on the specified date.
expire state role passwords – role passwords expire on the specified date.
date_time – expiration date.
“maximum failed logins”, -1 – This new value for maximum failed logins indicates that the failed login count in the syslogins column login count is updated whenever an authentication failure occurs, but that the account is not locked. Compare with a 0 value, which avoids incrementing the column for every failed authentication and avoids locking the account due to authentication failures. See “sp_configure” and “sp_modifylogin account” for additional changes to maximum failed login configuraiton.
For example, this expires all logins that begin with “bob” on March 2, 2007:
sp_passwordpolicy 'expire login passwords' 'bob%'
In the following example, roles with passwords that have not been changed after February 2, 2006 are expired:
sp_passwordpolicy 'expire role passwords', '020208'
