The sp_modifylogin stored procedure has a new value that lets you clear the previous setting for an option you set for a specific user. The value is 'clear' and uses this syntax for the "passwd expiration", "min passwd length", and "max failed_logins" options:
sp_modifylogin loginame, option, 'clear'
In this example, sp_modifylogin was used to set to the password expiration for a user named “John” to expire in 30 days, even though the system default for password expiration is 90 days:
1> sp_modifylogin 'John', 'passwd expiration', 30 2> go
Using 'clear', we can now remove this 30-day password expiration on John:
1> sp_modifylogin 'John', 'passwd expiration', 'clear' 2> go
If John’s password expiration setting had been changed previously, executing the command returns a message similar to the following, and John’s password expiration returns to being the same as the system default:
The login-specific 'passwd expiration' attribute has been removed.
If John’s password expiration was not manually changed from the system default, executing the command returns a message such as:
There is no login-specific 'passwd expiration' attribute set for this user.
This means that your sp_modifylogin changed nothing, and John’s password expiration is still the same as the system default.
