Setting and changing the maximum login attempts

Setting the maximum number of login attempts allowed provides protection against “brute-force” or dictionary-based attempts to guess passwords. A system security officer can specify a maximum number of consecutive login attempts allowed, after which the login or role is automatically locked. The number of allowable failed login attempts can be set for the entire server, or for individual logins and roles. Individual settings override the server-wide setting.

The number of failed logins is stored in the logincount column in master..syslogins. A successful login resets the number of failed logins to 0.

By default, maximum failed logins is turned off and this check is not applied to passwords. Use sp_passwordpolicy to set server-wide maximum number of failed logins for logins and roles.

1. To set the number of failed logins allowed, enter:

   sp_passwordpolicy 'set', 'maximum failed logins', 'number'
   

See sp_passwordpolicy in Reference Manual: Procedures.

1. To set the maximum number of failed login attempts for a specific login at creation, use create login.

This example creates the new login “joe” with the password “Djdiek3” and sets the maximum number of failed login attempts to 3:

create login joe with password Djdiek3 max failed attempts 3

See create login in Reference Manual: Commands.

1. To set the maximum failed logins for a specific role at creation, use create role.

This example creates “intern_role” with the password “temp244”, and sets the maximum failed logins for “inter_role” to 20:

create role intern_role with passwd "temp244", max failed_logins 20

See create role in Reference Manual: Commands.

1. Use alter login to set or change the maximum failed logins for an existing login.

Changes the maximum failed logins for the login “joe” to 40:

alter login joe modify max failed attempts 40 

1. Use alter role to set or change the maximum failed logins for an existing role.

This example changes the maximum failed logins allowed for “physician_role” to 5:

alter role physician_role set max failed_logins 5

This example removes the overrides for the maximum failed logins for all roles:

alter role "all overrides" set max failed_logins -1

For details on the syntax and rules for using maximum failed logins, see alter role in Reference Manual: Commands.