I have a use case in which I have to get last five password from passwordhistory table,and there two column one md5 encrypted and second is hash ,after retrieving five records then I have to compared these column with new password,after moving them from md5 and hash function,if any record found then return true else return false.
DECLARE @UserID INT = 6
DECLARE @Password NVARCHAR(200)='admin1952'
SELECT * FROM
( SELECT TOP 5
ChangedPassword AS ChangedPassword , PasswordChangeHistory.Password_Hash AS Password_Hash FROM PasswordChangeHistory WITH ( NOLOCK )
INNER JOIN [User] U WITH ( NOLOCK )
ON PasswordChangeHistory.UserID = U.Id WHERE PasswordChangeHistory.UserID = @UserID
) AS result WHERE PasswordChangeHistory.ChangedPassword = dbo.Encrypt_MD5(@Password)
OR PasswordChangeHistory.Password_Hash = dbo.Encrypt_SHA2(@Password)

Sajid HussainPosted May 10, 2017, 2:22 AM
ChangedPassword AS Changed ,PasswordChangeHistory.Password_Hash AS Password_Hash