allow username update/change with SqlMembershipProvider

[ibiza]

Hi,

what would be the simplest way to add a functionality so it is possible
to change the username in an asp.net 2.0 webapplication that uses
SqlMembershipProvider? By default, this providers does not allow to
update, but is there a quick way to enable it, without rewriting my
whole new MembershipProvider class?

thanks a lot,

ibiza

[bm0061]

Hi Ibiza,

The AspNetSQLMembershipProvider does not include the ability to change usernames and if you look at the stored procedures that are installed when you run AspNet_RegSQL.Exe (or that are created automatically by the providers), there is no stored procedure available to call.

I wrote an Asp.Net Server control called the Membership Manager Control that includes this much needed functionality so I happen to have handy a script to create the "ChangeUserName" stored procedure:

create PROCEDURE [dbo].[Aspnet_Membership_ChangeUserName]
    @ApplicationName                        nvarchar(256),
    @UserId                                 uniqueidentifier,
    @NewUserName                            nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
 
    DECLARE @ExistingUserId uniqueidentifier
    SELECT @ExistingUserId = NULL
 
    DECLARE @ErrorCode     int
    SET @ErrorCode = 0
 
    DECLARE @TranStarted   bit
    SET @TranStarted = 0
 
    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0
 
    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
 
    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END
 
 
    SELECT  @ExistingUserId = UserId FROM dbo.aspnet_Users WITH ( UPDLOCK, HOLDLOCK )
        WHERE LOWER(@NewUserName) = LoweredUserName AND @ApplicationId = ApplicationId
 
    IF ( @ExistingUserID IS NULL)
        BEGIN
            /* userName is available */
            UPDATE dbo.aspnet_Users SET UserName = @NewUserName, LoweredUserName= LOWER(@NewUserName) WHERE userID=@UserID
        END
    ELSE
        BEGIN
            IF( @ExistingUserID = @UserId)
                BEGIN
                    SET @ErrorCode = 1
                    GOTO Cleanup
                END
            ELSE
                BEGIN
                    SET @ErrorCode = 2
                    GOTO Cleanup
 
                END
        END
 
    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END
 
    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
    END
 
    RETURN 0
 
Cleanup:
 
    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END
 
    RETURN @ErrorCode
 
END

If you run that script, you will create a new stored procedure that you can call from your own code. You can also check out my blog at mishler.net that has other good material on the subject of Asp.Net membership and in particular, the capabilities and limitations of the AspNetSqlMembershipProvider.

Regards,

Brian Mishler
http://www.pocketforms.net