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!