The PWDCOMPARE function is really handy for further securing your SQL Servers by checking for a range of blank or common passwords. If you google for common password list you’ll probably recognise several if you’ve been working in IT for any reasonable amount of time. Fortunately you can use this function, in conjunction with the sys.sql_logins view, to check an instance for bad passwords.

I’m only checking ten common passwords with the addition of a blank one. The list is based on a study of UK passwords so it’s not going to suitable for all corners of the globe. For a live system check you’ll probably want to use a more comprehensive list of passwords.

This TSQL will list any sql logins with a bad password.

DECLARE @passwords TABLE
(
	pwd VARCHAR(100)
)

INSERT INTO @passwords
(
	pwd
)
SELECT 'thomas'
UNION ALL
SELECT 'arsenal'
UNION ALL
SELECT 'monkey'
UNION ALL
SELECT 'charlie'
UNION ALL
SELECT 'qwerty'
UNION ALL
SELECT '123456'
UNION ALL
SELECT 'letmein'
UNION ALL
SELECT 'liverpool'
UNION ALL
SELECT 'password'
UNION ALL
SELECT '123'
UNION ALL
SELECT '';

-- List bad users
SELECT l.[name],
	   l.[sid],
	   p.pwd
FROM sys.sql_logins l
INNER JOIN @passwords p
	ON PWDCOMPARE(p.pwd, l.password_hash) = 1;

Hopefully you’ll get no results from your check but if you do it may look something like this;

tsql_pwdcompare_sql_login_bad_password