WIDBA

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.


[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$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!

2 comments:

  1. You mean like this?

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

    ReplyDelete
    Replies
    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....

      Delete