Export (0) Print
Expand All
3 out of 4 rated this helpful - Rate this topic

PWDCOMPARE (Transact-SQL)

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.

Topic link icon Transact-SQL Syntax Conventions

PWDCOMPARE ( 'clear_text_password'
   , password_hash 
   [ , version ] )
' clear_text_password '

Is the unencrypted password. clear_text_password is sysname (nvarchar(128)).

password_hash

Is the encryption hash of a password. password_hash is varbinary(128).

version

Obsolete parameter that can be set to 1 if password_hash represents a value from a login earlier than SQL Server 2000 that was migrated to SQL Server 2005 or later but never converted to the SQL Server 2000 system. version is int.

Caution note Caution

This parameter is provided for backwards compatibility, but is ignored since password hash blobs now contain their own version descriptions. This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

int

Returns 1 if the hash of the clear_text_password matches the password_hash parameter, and 0 if it does not.

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.

PWDENCRYPT is available to public.

CONTROL SERVER permission is required to examine the password_hash column of sys.sql_logins.

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 ;
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.