I'm not sure where to post this to get a response, or how to get in touch with the MS membership team so I'll post here, and I've also put it up on forums.microsoft.com.

I've just begun to extend the membership framework for .NET. I've noticed that aspnet_Membership_GetUserByName has the potential to update LastActivityDate in aspnet_Users, but actually will not ever do the update. The "problem" (unless this is intentional) is that @UserId is never set in the select statement before the update statement, i.e. there is no "@UserId = u.UserId" in the Select.

So is this a problem with my installation, is it a "bug", or is it intentional?

Andy

What is your mean about "GetUserByName" method? I could not find it anywhere.

Sorry, I find it. It is stored procedure.

Just as you said, the update statement should not work I am not sure whether it is a bug. We need dev team to clarify.

Yes, sorry, I should have been clearer on that, its the stored procedure aspnet_Membership_GetUserByName that is the problem. I'm glad that you have the same problem.

Interestingly aspnet_Membership_GetUserByEmail doesn't even bother pretending that its going to update LastActivityDate, and aspnet_Membership_GetUserByUserId actually does the update (doesn't just pretend it will do it), so I'm becoming more confused about how this is meant to be used.

I've altered my aspnet_Membership_GetUserByName stored procedure heavily now in order to get it to do the update.

Is there anyone that doesn't have this version of the stored procedure (SQL pasted below)? How do we get the dev team to shed some light on this?

Andy

Original (framework) version of the sproc:

 

ALTER PROCEDURE [dbo].[aspnet_Membership_GetUserByName]
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
AS
BEGIN
    DECLARE @UserId uniqueidentifier

    IF (@UpdateLastActivity = 1)
    BEGIN
        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, @CurrentTimeUtc, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,m.LastLockoutDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found

            RETURN -1

        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        WHERE    @UserId = UserId <!-- USER ID NEVER SET, SO THIS WILL NOT UPDATE
    END
    ELSE
    BEGIN
        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,m.LastLockoutDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found

            RETURN -1
    END

    RETURN 0
END

 

To be clear, I have fixed this. What I want to know is why this is as it is. Is LastActivityDate actively used in the membership framework, and should this stored procedure be updating it or not? Obviously if it should then this is a problem as every new application which has the membership tables added to it by VS then needs to be fixed, or the membership tables need to be added by copying from a template database.

I initialized @userId variable with SQL SELECT command. This works for me:

CREATE PROCEDURE dbo.aspnet_Membership_GetUserByName
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
AS
BEGIN
    DECLARE @UserId uniqueidentifier

    IF (@UpdateLastActivity = 1)
    BEGIN

-- Dadi, 11.08.2006
-- Am adaugat eu cda 'select' de mai jos. Fara ea nu se poate executa cda  'update' care actualizeaza LastActivityTime.
--Pb. provine din faptul ca variabila @UserId nu era niciunde initializata

        SELECT TOP 1 @userId=u.UserId
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND    
   LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
-------------

        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, @CurrentTimeUtc, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,m.LastLockoutDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1

--pt. cda 'update de m. jos vezi detaliile de mai sus
        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        WHERE    @UserId = UserId
    END
    ELSE
    BEGIN
        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,m.LastLockoutDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1
    END

    RETURN 0
END
GO

 

Is there a patch for this issue? I've corrected the procedure, but it would be nice if the bug was acknowledged and patched.

 I can't believe more people haven't noticed this issue.

 

-Rick

I haven't heard/seen anything about a patch.

Nice catch!  I had been poking around on this for the last few hours and had seen some posts suggesting updates to the global.asax but your suggestion makes perfect sense.  Obviously the @Userid is not set and as such will never match.  I just modified my proc and it works correctly now.

 

Thanks,

Dave 

Nice catch!  I had been poking around on this for the last few hours and had seen some posts suggesting updates to the global.asax but your suggestion makes perfect sense.  Obviously the @Userid is not set and as such will never match.  I just modified my proc and it works correctly now.

 

Thanks,

Dave