sp_passwordpolicy is an interface that a user with a sso_role can use to specify, remove, and list new password complexity options. This information is stored in the master.dbo.sysattributes table.
sp_passwordpolicy {set | clear | list }, policy_option, option_value
where:
set – sets a value to an option.
clear – 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.
list – lists the values of the options specified.
policy_option – the option parameter on which to perform an operation. Valid options are:
disallow simple passwords – a 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.
option_value – is the value for the policy_option.
To set a password expiration warning interval to seven days before the password expires, enter:
sp_passwordpolicy 'set', 'password exp warn interval', 7
To list the option for minimum number of special characters, enter:
sp_passwordpolicy 'list', 'min special char in password'
To reset disallow simple password to the default value, enter:
sp_passwordpolicy 'clear', 'disallow simple passwords'
