Query for logins with "CHECK_POLICY=OFF"

  • I would like a script to run against a database server and find all the logins that have the check_policy=off. I think that will be the first step and finding logins that don't meet our corporate password policy. If anyone can please point me in right direction to where that value is stored, I'd really appreciate it.

    Thank you,

  • SELECT name

    ,type_desc

    ,default_database_name

    FROM sys.sql_logins

    WHERE is_policy_checked = 0

    ??


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thank you very much, This was exactly what I was looking for.

  • No problem. Also, this might help

    DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255))

    INSERT INTO @WeakPwdList(WeakPwd)

    SELECT ''

    UNION ALL SELECT '123'

    UNION ALL SELECT '1234'

    UNION ALL SELECT '12345'

    UNION ALL SELECT 'abc'

    UNION ALL SELECT 'default'

    UNION ALL SELECT 'guest'

    UNION ALL SELECT '123456'

    UNION ALL SELECT '@@Name123'

    UNION ALL SELECT '@@Name'

    UNION ALL SELECT '@@Name@@Name'

    UNION ALL SELECT 'admin'

    UNION ALL SELECT 'Administrator'

    UNION ALL SELECT 'admin123'

    SELECT t1.*, REPLACE(t2.WeakPwd,'@@Name',t1.name) As [Password]

    FROM sys.sql_logins t1

    INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1

    OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',t1.name),password_hash) = 1)

    WHERE t1.is_policy_checked = 0

    *can't remember where the script came from, but was useful earlier in the year when I was demonstrating to one of our director's why I thought certain users should have limited access to our data.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply