Audit Password Complexity

How do you know if your SQL logins are using weak passwords? This script loads a wordlist file (a plain text file with one candidate password per line) into a temp table, generates case variants of every word, and then runs PWDCOMPARE against each SQL login's hashed password. Any login whose password matches a word - or a simple case variant of one - is reported. It only tests SQL Server logins; Windows-authenticated accounts are skipped.

Point the BULK INSERT at your own wordlist file before running. The wordlist itself is not published here - it contains the candidate passwords we audit against.

SET NOCOUNT ON;
GO

CREATE TABLE #WordList
(
	[Word] SYSNAME NOT NULL
)
CREATE TABLE #Dictionary
(
	[Word] SYSNAME NOT NULL
)

--Load word list.
BULK INSERT #WordList FROM 'C:\Audit Password Complexity.txt';

--Make a distinct list of the word variants.
INSERT INTO #Dictionary SELECT DISTINCT LOWER(Word) FROM #WordList
INSERT INTO #Dictionary SELECT UPPER([Word]) FROM #WordList WHERE UPPER([Word]) <> [Word] COLLATE Latin1_General_BIN
INSERT INTO #Dictionary SELECT UPPER(SubString([Word], 1, 1)) + LOWER(SubString([Word], 2, 100))
FROM #WordList
WHERE UPPER(SubString([Word], 1, 1)) + LOWER(SubString([Word], 2, 100)) <> [Word] COLLATE Latin1_General_BIN

--Ensure that we look for blank passwords.
IF NOT EXISTS(SELECT 1 FROM #Dictionary WHERE [Word] = '')
BEGIN--IF
	INSERT INTO #Dictionary SELECT ''
END--IF

--Check the passwords.
SELECT
	UserAudit.[Username],
	UserAudit.[Password]
FROM
	(
		SELECT
			SL.name AS [Username],
			(SELECT TOP 1 D.[Word] FROM #Dictionary AS D WHERE PWDCOMPARE(D.[Word], SL.[password]) = 1) AS [Password]
		FROM
			master.sys.syslogins AS SL
		WHERE
			SL.isntgroup <> 1
			AND SL.isntname <> 1
			AND SL.isntuser <> 1
			--AND SL.name LIKE 'S%' --Only check specific usernames.
	) AS UserAudit
WHERE
	UserAudit.[Password] IS NOT NULL

--Cleanup.
DROP TABLE #WordList
DROP TABLE #Dictionary