How to check Enforce password expiration is set for SQL Login

05 August,2019 by Jack Vamvas

Question: We had an unexpected application outage due to a SQL Login which had Enforce password expiration set . Unfortunately this was not being monitored, and led to a situation where the password had expired and the application was able to logon onto the database.  It's the sql server team security policy to not have SQL Logins set with MUST_CHANGE  ON. 

Enforce_password_expiration

Part of enforcing and monitoring the policy includes incorporating certain checks in daily sql server security violations reports. What is the sql code to execute this check and to identify if any SQL Logins have Enforce Password Policy and Enforce password expiration set. ?

Answer: The sql code to execute and incorporate into your sql server security violations report is  just below

 

SELECT name,
is_expiration_checked  As 'is_expiration_checked'
FROM    sys.sql_logins
WHERE   is_policy_checked = 1 and is_expiration_checked = 1 

 

Read more on sql server security

 SQL Server - Security Risk Analysis and database security

SQL Server Security Policy


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to check Enforce password expiration is set for SQL Login


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer