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.
Setting the server-wide maximum
failed logins
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.
To set the number of failed logins allowed, enter:
sp_passwordpolicy 'set', 'maximum failed logins', number
See sp_passwordpolicy in the Reference Manual: Procedures.
Setting the maximum failed logins for
specific logins
To set the maximum failed logins for a specific login at creation, use sp_addlogin.
This example creates the new login “joe” with the password “Djdiek3” and sets the maximum number of failed login attempts for the login “joe” to 2:
sp_addlogin joe, "Djdiek3", pubs2, null, null, null, null, 2
See sp_addlogin in Reference Manual: Procedures.
Setting
the maximum failed logins for specific roles
To set the maximum failed logins for a specific role at creation, use create role.
This example creates the intern_role role with the password “temp244”, and sets the maximum failed logins for intern_role to 20:
create role intern_role with passwd "temp244", maximum failed logins 20
See create role Reference Manual: Commands.
Changing the maximum failed logins for
specific logins
Use sp_modifylogin to set or change the maximum failed logins for an existing login.
Changes the maximum failed logins for the login “joe” to 40:
sp_modifylogin "joe", "max failed_logins", "40"
The value parameter is a character datatype;
therefore, quotes are required for numeric values.
sp_modifylogin only effects user roles, not system roles. For details on the syntax and rules, see sp_modifylogin.
See sp_modifylogin in Reference Manual: Procedures.
Changing the maximum failed logins for
specific roles
Use alter role to set or change the maximum failed logins for an existing role.
Example 1 Changes the maximum failed logins allowed for physician_role to 5:
alter role "all overrides" set maximum failed logins -1
Example 2 Removes the overrides for the maximum failed logins for all roles:
alter role physician_role set maximum failed logins 5
For details on the syntax and rules for using maximum failed logins, see alter role.
