Storing Passwords in MS SQL

In MySQL, storing users' passwords is easily done by the function SHA1. Assuming a table usertable exists with two columns userid and passwrd.

INSERT INTO usertable(userid, passwrd) 
VALUES('johnlim', SHA1('SECRET'));

To retrieve the user's record,

SELECT * FROM usertable
 WHERE userid = 'johnlim'
   AND passwrd = SHA1('SECRET');

Is there an equivalent in MS SQL Server? Yes! Recently, MS SQL Server 2005 has nicely built-in support for hashing and the function is called HASHBYTES. This function takes in two string parameters. The first determines the algorithm used to provide the hash. Possible values for the algorithm are MD2, MD4, MD5, SHA and SHA1. The second takes in the value to be hashed.

Hence the equivalent SQL statements for MS SQL are

INSERT INTO usertable(userid, passwrd) 
VALUES('johnlim', HASHBYTES('SHA1', 'SECRET'));

SELECT * FROM usertable
 WHERE userid = 'johnlim'
   AND passwrd = HASHBYTES('SHA1', 'SECRET');

The only difference is that the passwrd column in MySQL is VARCHAR while in MS SQL is VARBINARY.