PWDCOMPARE–a hidden function of SQL SERVER TIP #88

 

I the last tip TIP#88 we saw how to encrypt a password. Now in this tip I would like to share how to check encrypted password ?

Means once you stored your encrypted password in database now next step is to compare that particular password with your input password and return results accordingly.

The Syntax of the PWDCOMPARE   is very simple

PWDCOMPARE(‘Password plain text’, ‘Password encrypted form’)

This function return 1 if plain text and hash value  are matched else return o.

For example

Lets suppose we have created a table with 3 columns like userId, username and password

as shown below

DECLARE @tblLogin AS TABLE (UserId INT IDENTITY,
                            Username  VARCHAR(100),
                            EncryptedPassword NVARCHAR(MAX))

Now suppose we have inserted 2 rows in to it wit encrypted password

INSERT INTO @tblLogin VALUES ('Indiandotnet',PWDENCRYPT(N'MyPassword'))
INSERT INTO @tblLogin VALUES ('SQLRaaga', PWDENCRYPT(N'Test'))

Now, Suppose we have want to write a query which return rows from @tbllogin whose password is Test then it should return SQL Raaga for this

I have to write following query

SELECT * FROM @tblLogin WHERE PWDCOMPARE(N'Test',EncryptedPassword) = 1

For detail  take a look of below snap

 

I hope you understand with above provided  example.

 

Enjoy !!!

RJ!!