SqlMembershipProvider doesn't update last activity time - by Richard Deeming

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 306297 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 10/23/2007 6:05:27 AM
Access Restriction Public

Description

The GetUser(string, bool) method of the SqlMembershipProvider doesn't update the LastActivityDate column of the aspnet_Users table, even though the userIsOnline parameter is set to "true".

This is due to an error in the "dbo.aspnet_Membership_GetUserByName" stored procedure, which will only update rows where the UserId column is Null.
Sign in to post a comment.
Posted by Thomas Olsson on 6/12/2008 at 2:58 AM
This is not fixed in .NET Framework 2.0 SP1 (which is part of .NET Framework 3.5)
Posted by Microsoft on 10/25/2007 at 7:49 AM
We'll look to fix this bug in an upcoming service pack of the .NET Framework. In the meantime, for a workaround, you can either modify the "InstallMembership.sql" script in the framework directory like you've suggested or alternatively, after you make the call to "GetUser()", you can take the returned "MembershipUser" object and pass it into "Membership.UpdateUser". This would cause the database to be updated with the correct LastActivityDate.

Again, thanks so much for reporting this issue.

The ASP.NET Team
Posted by Microsoft on 10/24/2007 at 1:32 PM
Thanks for the repro files. We can now repro the issue. Thanks for reporting this. We'll continue our investigation and get back to you.

Thanks,
The ASP.NET Team
Posted by Richard Deeming on 10/24/2007 at 11:03 AM
I have now attached a simple demonstration project. Run the project, and log in as "test.user" / "password$". Refresh the default.aspx page, and notice that the LastActivityDate column in the GridView is not updated to match the MembershipUser object.
Posted by Richard Deeming on 10/24/2007 at 11:00 AM
No, the API only updates the LastActivityDate value on the MembershipUser object, not in the database. I will attach a very simple demonstration.
Posted by Microsoft on 10/24/2007 at 10:20 AM
We're unable to reproduce this problem. Creating a simple page with calls the specific API is updating the user's LastActivityDate.

Can you attach the exact website you're trying? Perhaps there are some other factors at play that's causing this behavior.

Thanks,
ASP.NET Team
Posted by Richard Deeming on 10/23/2007 at 6:10 AM
The aspnet_Membership_GetUserByName procedure declares a local variable called @UserId, fails to initialize it, and then updates the aspnet_Users table based on the variable:

DECLARE @UserId uniqueidentifier

IF (@UpdateLastActivity = 1)
BEGIN
    SELECT TOP 1 ...
    ...
    UPDATE dbo.aspnet_Users
    SET         LastActivityDate = @CurrentTimeUtc
    WHERE    @UserId = UserId

To fix the problem, the procedure must be updated to initialize the @UserId variable. The corrected procedure should look something like this:

CREATE PROCEDURE dbo.aspnet_Membership_GetUserByName
    @ApplicationName     nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc     datetime,
    @UpdateLastActivity bit = 0
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    
    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
    
    If (@@ROWCOUNT = 0) -- Username not found
        RETURN -1
    
    IF (@UpdateLastActivity = 1)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET         LastActivityDate = @CurrentTimeUtc
        WHERE    @UserId = UserId
    END
    
    SELECT 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_Users u, dbo.aspnet_Membership m
    WHERE @UserId = u.UserId AND u.UserId = m.UserId

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

    RETURN 0
END