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
To set the server-wide maximum failed logins for logins and roles, use the maximum failed logins configuration parameter.
This example sets the system-wide maximum failed logins to 5:
sp_configure "maximum failed logins", 5
For details on the syntax and rules for using maximum failed logins, see sp_configure.
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
For details on the syntax and rules for using maximum failed logins, see sp_addlogin.
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
For details on the syntax and rules for using maximum failed logins, see create role.
Changing the maximum failed logins for
specific logins
Use sp_modifylogin to set or change the maximum failed logins for an existing login.
Example 1 Changes the maximum failed logins for the login “joe” to 40:
sp_modifylogin "joe", @option="maximum failed logins", @value="40"
The value parameter is a character datatype;
therefore, quotes are required for numeric values.
Example 2 Changes the overrides for maximum failed logins for all logins to 3:
sp_modifylogin "all overrides", @option="maximum failed logins", @value="-1"
Example 3 Removes the overrides for maximum failed logins option for all logins:
sp_modifylogin "all overrides", "maximum failed logins", "3"
sp_modifylogin only effects user roles, not system roles. For details on the syntax and rules for using maximum failed logins, see sp_modifylogin.
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.
