Thanks Joe, well after digging through and debugging using the source code i was able to pinpoint where the problem is.

Parameter count for [mp_Users_SelectByLoginName] didn't match. For some reason it was not updated. I added (@AllowEmailFallback bit) and it works now. 

Thanks again. :)

Looks like last parameter timeZoneId is missing in the stored procedure 

ALTER Procedure [dbo].[mp_Users_Insert]

/*
Author:            Joe Audette
Created:        2004-09-30
Last Modified:    2011-12-29

*/

@SiteGuid    uniqueidentifier,
@SiteID    int,
@Name         nvarchar(100),
@LoginName    nvarchar(50),
@Email        nvarchar(100),
@Password     nvarchar(1000),
@PasswordSalt nvarchar(128),
@UserGuid    uniqueidentifier,
@DateCreated datetime,
@MustChangePwd bit,
@FirstName         nvarchar(100),
@LastName         nvarchar(100)

I did modify the stored procedure but still same error.

ALTER Procedure [dbo].[mp_Users_Insert]

/*
Author:            Joe Audette
Created:        2004-09-30
Last Modified:    2011-12-29

*/

@SiteGuid    uniqueidentifier,
@SiteID    int,
@Name         nvarchar(100),
@LoginName    nvarchar(50),
@Email        nvarchar(100),
@Password     nvarchar(1000),
@PasswordSalt nvarchar(128),
@UserGuid    uniqueidentifier,
@DateCreated datetime,
@MustChangePwd bit,
@FirstName         nvarchar(100),
@LastName         nvarchar(100),
@TimeZoneId         nvarchar(32),
@EmailChangeGuid    uniqueidentifier

AS
INSERT INTO         [dbo].mp_Users
(
        SiteGuid,
            SiteID,
                [Name],
            LoginName,
                Email,
                [Pwd],
            UserGuid,
            DateCreated,
            TotalRevenue,
            MustChangePwd,
            RolesChanged,
            FirstName,
            LastName,
            TimeZoneId,
            EmailChangeGuid,
            PasswordResetGuid,
            PasswordSalt
    

)

VALUES
(
            @SiteGuid,
            @SiteID,
                @Name,
            @LoginName,
                @Email,
                @Password,
            @UserGuid,
            @DateCreated,
            0,
            @MustChangePwd,
            0,
            @FirstName,
            @LastName,
            @TimeZoneId,
            @EmailChangeGuid,
            '00000000-0000-0000-0000-000000000000',
            @PasswordSalt
)

SELECT        @@Identity As UserID

I was on a older version and i did upgrade... may be something wrong happened. I will try to upgrade it. Thanks.