create function [dbo].[security_GetPasswordHash]
(
    @Password sysname,
    @Salt uniqueidentifier
)

returns binary(128) with schemabinding, returns null on null input as begin

declare @CertId int, @CertPwd sysname;

set @CertId = ...; -- Get your cert however you like it
set @CertPwd = ...; -- If your cert is encrypted with password, get it too

return SignByCert(
    @CertId,
    SignByCert(@CertId, @Password, @CertPwd) + cast(@Salt as binary(16)),
    @CertPwd
);
end;
go

-- Try to validate the user
select @UserId = u.Id
from dbo.Users u
where u.LoginName = @Login
    and u.PasswordHash = dbo.security_GetPasswordHash(@Password, u.PasswordSalt);

-- Special case of user existence - there may be a wrong password here, too.
if @UserId is null begin
    -- The specified user either does not exist, or wrong password has been supplied.
    set @Error = 51008;
    set @Message = dbo.sys_FormatErrorMessage(@Error, @CultureId, default, default, default, default);
    throw 50000, @Message, 1;
end;