Try this
Create table users
( Userid int,
Pwd VARBINARY(32) )
INSERT INTO users VALUES (3,CONVERT(VARBINARY(32),PWDENCRYPT('TTT')))
Select * from users
Declare @Valid int
SELECT @Valid = PWDCOMPARE('TTT', Pwd) FROM users where Userid = 3
Select @Valid = Isnull (@Valid,0)
Print @Valid
-- Returns 1
SELECT @Valid = PWDCOMPARE('TTT', Pwd) FROM users where Userid = 2
Select @Valid = Isnull (@Valid,0)
Print @Valid
-- Returns 0