Keeping your Server Secure Part 1 : Login Password Policy
Now we are going to cover very important activity from a DBA perspective. Database\Server Security is the one of the most important thing on which any organisation relies solely on Database Administrators. Each company has its own sets of Policies applied to Logins & Users and these policies should be thoroughly reviewed as per client’s expectation and requirements.
Today I am going to provide you one very handy script which you can use to check different policies applied on all Logins on a given SQL Instance. This script will check and report out below Policies for all Logins :-
- Password Expiration Check
- Bad Password Attempt
- Bad Password Attempt time
- Days Until Password Expiration
- Is password Expired
- Is Login Locked
- Does Password must needs to be changed during next login
- Locked out time due to failed login attempts
- Time when current password was set.
You can add and remove parameters to modify the script as per your requirement. Also you can configure below script using SSRS to keep the Login Policy in check from time to time.
SET NOCOUNT ON SELECT name, CASE CAST(is_policy_checked AS TINYINT) + CAST(is_expiration_checked AS TINYINT) WHEN 0 THEN 'Not Enforced' WHEN 1 THEN 'Password - No Expiration' WHEN 2 THEN 'Password With Expiration' END AS PasswordEnforcement , LOGINPROPERTY(name,'BadPasswordCount') AS BadPasswordCount, LOGINPROPERTY(name,'BadPasswordTime') AS BadPasswordTime, LOGINPROPERTY(name,'DaysUntilExpiration') AS DaysUntilExpiration,default_database_name, CASE WHEN LOGINPROPERTY(name,'IsExpired') = 0 THEN 'NO' ELSE 'YES' END AS IsExpired, CASE WHEN LOGINPROPERTY(name,'IsLocked') = 0 THEN 'NO' ELSE 'YES' END AS IsLocked, CASE WHEN LOGINPROPERTY(name,'IsMustChange') = 0 THEN 'NO' ELSE 'YES' END AS IsMustChange, LOGINPROPERTY(name,'LockoutTime') AS LockoutTime, LOGINPROPERTY(name,'PasswordLastSetTime') AS PasswordLastSetTime FROM sys.sql_logins ORDER BY name