sp_passwordpolicy

Allows a user with sso_role to configure login and password policy options.

Syntax

To specify, remove, and list new password complexity options:
sp_passwordpolicy {“set” | “clear” | “list”}, policy_option, option_value
To verify the password complexity options:
sp_passwordpolicy 'validate password options'
To generate asymmetric key pairs for network login password encryption:
sp_passwordpolicy "regenerate keypair"
To expire passwords:
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"
To display a brief description of all commands, options, and their values:
sp_passwordpolicy "help"

Parameters

Examples

Usage

sp_passwordpolicy information is stored in the master.dbo.sysattributes table.

Once the SAP ASE server has regenerated a new RSA key pair, subsequent generations use a formula of the last time when RSA key pair was generated, combined with the value you specified for keypair regeneration frequency.

The value of keypair regeneration period is stored in master..sysattributes under a new password policy class.

A default value of NULL for the option indicates that this row does not exist in sysattributes and the key pair is generated on when the SAP ASE server is restarted, and every 24 hours thereafter.

These two stored procedures do the same thing:
sp_passwordpolicy 'set', 'keypair regeneration period', NULL [,
	datetime of first generation] 
sp_passwordpolicy 'regenerate keypair' [, datetime of first generation] 

These global variable use the information from keypair regeneration period:

  • @@lastkpgendate – reflects the datetime of when the last key pair was generated.

  • @@nextkpgendate – to reflect when the key pair is next generated.

Permissions

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

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage security configuration privilege.

Disabled

With granular permissions disabled, you must be a user with sso_role.

Auditing

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'

The “password” audit option also audits the administration of RSA key pair regeneration period that generates the AUD_EVT_PASSWORD_ADMIN(115) auditing event.