WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Monday, March 26, 2012

Putting on your SOX - CHECK_POLICY

Here is another quick one for those of us who need to prove that all SQL logins on a SQL Instance have the "Enforce password policy" checked.  I couldn't find the T-SQL way to get a list, but Powershell gave me the solution in -le 1 minute.

$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "YourSQLInstance"

# Get All SQL Logins that don't have the enforce bit set to true.

$sqlServer.Logins | Where{$_.LoginType -eq "SqlLogin" -and $_.PasswordPolicyEnforced -eq $false} | Select Parent, Name, LoginType , PasswordPolicyEnforced

Happy Auditing!


  1. You mean like this?

    select name, is_policy_checked, is_expiration_checked, LOGINPROPERTY([name], 'Passwordlastsettime') from sys.sql_logins

    1. Thanks, was spending too much time trying to find which system table it was in :) Sadly it was easier to do with PS and SMO for me....