How to convert varbinary to GUID in TSQL stored procedure?

how can I convert the HASHBYTES return value to a GUID开发者_运维知识库?

This is what I have so far.

CREATE PROCEDURE [dbo].[Login]
    @email nvarchar,
    @password varchar
AS
BEGIN
    DECLARE @passHashBinary varbinary; 
    DECLARE @newPassHashBinary varbinary;

    -- Create a unicode (utf-16) password
    Declare @unicodePassword nvarchar;
    Set @unicodePassword = CAST(@password as nvarchar); 

    SET @passHashBinary = HASHBYTES('md5', @password);
    SET @newPassHashBinary = HASHBYTES('md5', @unicodePassword);

Simply cast it:

select cast(hashbytes('md5','foo') as uniqueidentifier)

But there are two questions lingering:

• why cast HASHBYTES to guid? Why not use the appropriate type for storage, namely BINARY(16)
• I hope you are aware that MD5 hashing passwords is basically useless, right? Because of rainbow tables. You need to use a secure hashing scheme, like an HMAC or the HA1 of Digest.