Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Hashes a password and compares the hash to the hash of an existing password. PWDCOMPARE can be used to search for blank SQL Server login passwords or common weak passwords.
Applies to: SQL Server (SQL Server 2008 through current version).
The PWDCOMPARE function is not a threat against the strength of password hashes because the same test could be performed by trying to log in using the password provided as the first parameter.
PWDCOMPARE cannot be used with the passwords of contained database users. There is no contained database equivalent.
A. Identifying logins that have no passwords
The following example identifies SQL Server logins that have no passwords.
SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('', password_hash) = 1 ;
B. Searching for common passwords
To search for common passwords that you want to identify and change, specify the password as the first parameter. For example, execute the following statement to search for a password specified as password.
SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('password', password_hash) = 1 ;