Simple data comparision will not work here.
select UserName,convert(varchar(10),DECRYPTBYPASSPHRASE('12',Password))AS Pwd
From UserAccount where UserName=@username and Password=@password
Change above to
select UserName,convert(varchar(10),DECRYPTBYPASSPHRASE('12',Password))AS Pwd
From UserAccount where UserName=@username and convert(varchar(50),DECRYPTBYPASSPHRASE('12',password))=@password
Create TABLE myUsers (user_id varchar(20), user_password varbinary(100));
Insert into myUsers values ('firstuser', EncryptByPassPhrase('12','pass'))
Insert into myUsers values ('seconduser', EncryptByPassPhrase('12','pass2'))
select * from myUsers
select * from myUsers Where user_id = 'firstuser'
and convert(varchar(50),DECRYPTBYPASSPHRASE('12',user_password)) = 'pass'