An interface that a user with sso_role can use to configure password complexity options. This information is stored in the master.dbo.sysattributes table.
To specify, remove, and list new password complexity options:
sp_passwordpolicy {“set” | “clear” | “list”}, policy_option, option_value
To generate asymmetric key pairs for network login password encryption:
sp_passwordpolicy "enable last login updates"
sp_passwordpolicy "expire role passwords", "[rolename | wildcard]"
sp_passwordpolicy "expire login passwords", "[login_name | wildcard]"
sp_passwordpolicy "expire stale role passwords", "datetime"
sp_passwordpolicy "expire stale login passwords", "datetime"
sp_passwordpolicy "regenerate keypair"
sets a value to an option. When using set, you must specify the policy_option.
deletes the row for the option specified in the master.dbo.sysattributes table. If there is no policy option specified, clear deletes all the option rows in the sysattributes table. When using clear, you must specify the policy_option.
lists the values of the options specified. When using list, you must specify the policy_option.
is the option parameter for set, clear, and list, with option_value being the their values:
Option |
Description |
|---|---|
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. |
disallow simple passwords |
Value of 1 turns this option on, and a value of 0 turns it off. |
min digits in password |
Indicates the minimum number of digits to be allowed in a password. |
min alpha in password |
Indicates the minimum number of alphabetic characters in a password. |
min special char in password |
Indicates the minimum number of special characters allowed in a password. |
min upper char in password |
Indicates the minimum number of upper-case characters allowed in a password |
min lower char in password |
Indicates the minimum number of lower case characters allowed in a password. |
systemwide password expiration |
Indicates the system-wide password expiration in days. |
password exp warn interval |
Indicates the password expiration warning interval in days. |
minimum password length |
Sets the minimum length of the password. |
maximum failed logins |
Sets the maximum number of failed logins allowed in a session before the account is locked. |
expire login |
Specifies that a login status changes to expired status when you create or reset your login. You are required to change your password on your first login. |
enables or disables 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.
expires login passwords, all logins or logins matching a wild card pattern. The column status in master database catalog syslogins is updated with a status bit LOGIN_EXPIRED (0x4) to indicate the password is expired.
expires the password of a role, all roles or roles matching a wild-card pattern. The column status in master database catalog syssrvroles is updated with a status bit ROLE_EXPIRED (0x4) to indicate the password is expired:
expires login passwords have not been changed after a datetime specified. The column status in master database catalog syslogins is updated with a status bit LOGIN_EXPIRED (0x0004) to indicate that the password is expired. See “Entering Date and Time Data” in Adaptive Server 15.0 Reference Manual: Building Blocks, Chapter 1, “System and User Defined Datatypes” for an explanation of how datetime values are entered.
expires role passwords have not been changed after a datetime specified. The column status in master database catalog syssrvroles is updated with a status bit ROLE_EXPIRED (0x4) to indicate the password is expired.
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.
Sets a password expiration warning interval to seven days before the password expires:
sp_passwordpolicy 'set', 'password exp warn interval', 7
Lists the option for minimum number of special characters:
sp_passwordpolicy 'list', 'min special char in password'
Resets disallow simple passwords to the default value:
sp_passwordpolicy 'clear', 'disallow simple passwords'
The set and clear commands in sp_passwordpolicy are audited through audit event 115, “Password Administration.”
A audit option “password” audits these actions.
sp_passwordpolicy 'set', 'option_name', 'option_value'
sp_passwordpolicy 'clear', 'option_name'
sp_passwordpolicy 'expire login passwords'
sp_passwordpolicy 'expire stale login passwords'
sp_passwordpolicy 'regenerate keypair'
sp_passwordpolicy 'expire role passwords'
sp_passwordpolicy 'expire stale role passwords'
