Find weak login passwords in your server

  • Comments posted to this topic are about the item Find weak login passwords in your server

  • Can also add password REVERSE option and add login default database owner to select clause

    SELECT sql_logins.name AS [LoginName],

    CASE

    WHEN PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',REVERSE(sql_logins.name)),password_hash) = 0 THEN REPLACE(t2.WeakPwd,'@@Name',sql_logins.name)

    ELSE REPLACE(t2.WeakPwd,'@@Name',REVERSE(sql_logins.name))

    END AS [Password]

    ,sql_logins.default_database_name,sql_logins.is_policy_checked,sql_logins.is_expiration_checked,sql_logins.is_disabled

    ,(SELECT suser_sname(owner_sid) FROM sys.databases WHERE databases.name = sql_logins.default_database_name) AS database_owner

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

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

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

    --WHERE sql_logins.is_disabled=0

    ORDER BY sql_logins.name

  • Have you considered simply enforcing password policy?

  • I found that if I included 'password', 'PASSWORD', and 'Password' in the @WeakPwdList table variable the script would not return all users with those passwords. The fix was to use UNION ALL instead of UNION. Thanks for the code. This is very useful.

  • Hi,

    Here is a script that will work for SQL2000 too.

    DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255))

    --Define weak password list

    --Use @@Name if users password contain their name

    INSERT INTO @WeakPwdList(WeakPwd)

    SELECT ''

    UNION SELECT '123'

    UNION SELECT '1234'

    UNION SELECT '12345'

    UNION SELECT 'abc'

    UNION SELECT 'default'

    UNION SELECT 'guest'

    UNION SELECT '123456'

    UNION SELECT '@@Name123'

    UNION SELECT '@@Name'

    UNION SELECT '@@Name@@Name'

    UNION SELECT 'admin'

    UNION SELECT 'Administrator'

    UNION SELECT 'admin123'

    -- SELECT * FROM @WeakPwdList

    SELECT syslogins.name AS [LoginName],

    CASE

    WHEN PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',REVERSE(syslogins.name)),password) = 0 THEN REPLACE(t2.WeakPwd,'@@Name',syslogins.name)

    ELSE REPLACE(t2.WeakPwd,'@@Name',REVERSE(syslogins.name))

    END AS [Password]

    ,syslogins.dbname as Default_Database

    ,(SELECT suser_sname(sid) FROM sysdatabases WHERE sysdatabases.name = syslogins.dbname) AS database_owner

    FROM syslogins INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password) = 1

    OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',syslogins.name),password) = 1

    OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',REVERSE(syslogins.name)),password) = 1 )

    --WHERE syslogins.is_disabled=0

    ORDER BY syslogins.name

  • Hi, i get this error, any help?.

    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the replace operation.(42000,468)

    thanks

  • Add COLLATE DATABASE_DEFAULT to table variable definition

    DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255) COLLATE DATABASE_DEFAULT )

  • it works, thanks.

  • see here

  • Thanks for the script.

Viewing 10 posts - 1 through 10 (of 10 total)

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