Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to check Enforce a Password policy is enabled on SQL Server

14 December,2021 by Tom Collins

Question: Using t-sql , How can  check I Enforce a password policy is enabled on SQL Server ? I want to add this check for a regular SQL Server security configuration scan. 

This information would be viewed via a report , which DBAs could fix the issue 

Answer: This view will display the current status of Enforce password policy on a SQL Server login.

select * from sys.sql_logins where is_policy_checked = 0 


This query checks if a SQL Server using SQL Authentication has the Enforce Password Policy enabled 





If the Enforce a Password policy is not set , the password does not to apply the password complexity - typically set by the OS rules.   

If you then change the flag to Enforce a Password policy, WITHOUT changing the password ,  this will not force you to change the password - if the password doesn't follow the password complexity rules. .  But if you attempt to change the password after you have clicked OK for Enforce a Password policy, the "Password validation failed" message will appear - if the password complexity rules are not met. 


Powershell script to execute the query across multiple servers

The requirement may be to run this query across multiple SQL Servers , either as a one-off or as a regular  execute on multiple SQL Servers. Powershell is a good solution to execute this script across multiple SQL Servers. 

This powershell script returns the sql logins with is_policy_checked = 0 from the  SQL Server Instances listed in the instances_all.txt file



The output is written to the host.  Read How to covert Powershell to HTML




$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=master;Integrated Security=sspi"
$sql = $cn.CreateCommand()
$sql.CommandText = "select @@servername as server_name, [name] as sql_login, type_desc,is_policy_checked from sys.sql_logins where is_policy_checked = 0 order by @@servername"
$rdr = $sql.ExecuteReader()

$dt |select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray  | out-host


Read more about password management

How to check Enforce password expiration is set for SQL Login

How to view SQL Server Password Configurations

How to check SQL Server password policy

Powershell sql server security audit

Author: Tom Collins (


Verify your Comment

Previewing your Comment

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

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.


Post a comment on How to check Enforce a Password policy is enabled on SQL Server | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer