set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTERPROCEDURE[dbo].[aspnet_Membership_CreateUser] @ApplicationNamenvarchar(256), @UserNamenvarchar(256), @Passwordnvarchar(128), @PasswordSaltnvarchar(128), @Emailnvarchar(256), @PasswordQuestionnvarchar(256), @PasswordAnswernvarchar(128), @IsApprovedbit, @CurrentTimeUtcdatetime, @CreateDatedatetime=NULL, @UniqueEmailint=0, @PasswordFormatint=0, @UserIduniqueidentifier OUTPUT AS BEGIN DECLARE@ApplicationIduniqueidentifier SELECT@ApplicationId=NULL DECLARE@NewUserIduniqueidentifier SELECT@NewUserId=NULL DECLARE@IsLockedOutbit SET@IsLockedOut=0 DECLARE@LastLockoutDatedatetime SET@LastLockoutDate=CONVERT( datetime, '17540101', 112 ) DECLARE@FailedPasswordAttemptCountint SET@FailedPasswordAttemptCount=0 DECLARE@FailedPasswordAttemptWindowStartdatetime SET@FailedPasswordAttemptWindowStart=CONVERT( datetime, '17540101', 112 ) DECLARE@FailedPasswordAnswerAttemptCountint SET@FailedPasswordAnswerAttemptCount=0 DECLARE@FailedPasswordAnswerAttemptWindowStartdatetime SET@FailedPasswordAnswerAttemptWindowStart=CONVERT( datetime, '17540101', 112 ) DECLARE@NewUserCreatedbit DECLARE@ReturnValueint SET@ReturnValue=0 DECLARE@ErrorCodeint SET@ErrorCode=0 DECLARE@TranStartedbit SET@TranStarted=0 IF( @@TRANCOUNT=0 ) BEGIN BEGINTRANSACTION 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 SET@CreateDate=@CurrentTimeUtc SELECT@NewUserId= UserId FROM dbo.aspnet_Users WHERELOWER(@UserName) = LoweredUserName AND@ApplicationId= ApplicationId IF ( @NewUserIdISNULL ) BEGIN SET@NewUserId=@UserId EXEC@ReturnValue= dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT SET@NewUserCreated=1 END ELSE BEGIN SET@NewUserCreated=0 IF( @NewUserId<>@UserIdAND@UserIdISNOTNULL ) BEGIN SET@ErrorCode=6 GOTO Cleanup END END IF( @@ERROR<>0 ) BEGIN SET@ErrorCode=-1 GOTO Cleanup END IF( @ReturnValue=-1 ) BEGIN SET@ErrorCode=10 GOTO Cleanup END IF ( EXISTS ( SELECT UserId FROM dbo.aspnet_Membership WHERE@NewUserId= UserId ) ) BEGIN SET@ErrorCode=6 GOTO Cleanup END SET@UserId=@NewUserId IF (@UniqueEmail=1) BEGIN IF (EXISTS (SELECT* FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK ) WHERE ApplicationId =@ApplicationIdAND LoweredEmail =LOWER(@Email))) BEGIN SET@ErrorCode=7 GOTO Cleanup END END IF (@NewUserCreated=0) BEGIN UPDATE dbo.aspnet_Users SET LastActivityDate =@CreateDate WHERE@UserId= UserId IF( @@ERROR<>0 ) BEGIN SET@ErrorCode=-1 GOTO Cleanup END END INSERTINTO dbo.aspnet_Membership ( ApplicationId, UserId, Password, PasswordSalt, Email, LoweredEmail, PasswordQuestion, PasswordAnswer, PasswordFormat, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart ) VALUES ( @ApplicationId, @UserId, @Password, @PasswordSalt, @Email, LOWER(@Email), @PasswordQuestion, @PasswordAnswer, @PasswordFormat, @IsApproved, @IsLockedOut, @CreateDate, @CreateDate, @CreateDate, @LastLockoutDate, @FailedPasswordAttemptCount, @FailedPasswordAttemptWindowStart, @FailedPasswordAnswerAttemptCount, @FailedPasswordAnswerAttemptWindowStart ) IF( @@ERROR<>0 ) BEGIN SET@ErrorCode=-1 GOTO Cleanup END IF( @TranStarted=1 ) BEGIN SET@TranStarted=0 COMMITTRANSACTION END RETURN0 Cleanup: IF( @TranStarted=1 ) BEGIN SET@TranStarted=0 ROLLBACKTRANSACTION END RETURN@ErrorCode END
set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTERPROCEDURE[dbo].[aspnet_Membership_CreateUser] @ApplicationNamenvarchar(256), @UserNamenvarchar(256), @Passwordnvarchar(128), @PasswordSaltnvarchar(128), @Emailnvarchar(256), @PasswordQuestionnvarchar(256), @PasswordAnswernvarchar(128), @IsApprovedbit, @CurrentTimeUtcdatetime, @CreateDatedatetime=NULL, @UniqueEmailint=0, @PasswordFormatint=0, @UserIduniqueidentifier OUTPUT AS BEGIN DECLARE@ApplicationIduniqueidentifier SELECT@ApplicationId=NULL DECLARE@NewUserIduniqueidentifier SELECT@NewUserId=NULL DECLARE@IsLockedOutbit SET@IsLockedOut=0 DECLARE@LastLockoutDatedatetime SET@LastLockoutDate=CONVERT( datetime, '17540101', 112 ) DECLARE@FailedPasswordAttemptCountint SET@FailedPasswordAttemptCount=0 DECLARE@FailedPasswordAttemptWindowStartdatetime SET@FailedPasswordAttemptWindowStart=CONVERT( datetime, '17540101', 112 ) DECLARE@FailedPasswordAnswerAttemptCountint SET@FailedPasswordAnswerAttemptCount=0 DECLARE@FailedPasswordAnswerAttemptWindowStartdatetime SET@FailedPasswordAnswerAttemptWindowStart=CONVERT( datetime, '17540101', 112 ) DECLARE@NewUserCreatedbit DECLARE@ReturnValueint SET@ReturnValue=0 DECLARE@ErrorCodeint SET@ErrorCode=0 DECLARE@TranStartedbit SET@TranStarted=0 IF( @@TRANCOUNT=0 ) BEGIN BEGINTRANSACTION 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 SET@CreateDate=@CurrentTimeUtc SELECT@NewUserId= UserId FROM dbo.aspnet_Users WHERELOWER(@UserName) = LoweredUserName AND@ApplicationId= ApplicationId IF ( @NewUserIdISNULL ) BEGIN SET@NewUserId=@UserId EXEC@ReturnValue= dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT SET@NewUserCreated=1 END ELSE BEGIN SET@NewUserCreated=0 IF( @NewUserId<>@UserIdAND@UserIdISNOTNULL ) BEGIN SET@ErrorCode=6 GOTO Cleanup END END IF( @@ERROR<>0 ) BEGIN SET@ErrorCode=-1 GOTO Cleanup END IF( @ReturnValue=-1 ) BEGIN SET@ErrorCode=10 GOTO Cleanup END IF ( EXISTS ( SELECT UserId FROM dbo.aspnet_Membership WHERE@NewUserId= UserId ) ) BEGIN SET@ErrorCode=6 GOTO Cleanup END SET@UserId=@NewUserId IF (@UniqueEmail=1) BEGIN IF (EXISTS (SELECT* FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK ) WHERE ApplicationId =@ApplicationIdAND LoweredEmail =LOWER(@Email))) BEGIN SET@ErrorCode=7 GOTO Cleanup END END IF (@NewUserCreated=0) BEGIN UPDATE dbo.aspnet_Users SET LastActivityDate =@CreateDate WHERE@UserId= UserId IF( @@ERROR<>0 ) BEGIN SET@ErrorCode=-1 GOTO Cleanup END END INSERTINTO dbo.aspnet_Membership ( ApplicationId, UserId, Password, PasswordSalt, Email, LoweredEmail, PasswordQuestion, PasswordAnswer, PasswordFormat, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart ) VALUES ( @ApplicationId, @UserId, @Password, @PasswordSalt, @Email, LOWER(@Email), @PasswordQuestion, @PasswordAnswer, @PasswordFormat, @IsApproved, @IsLockedOut, @CreateDate, @CreateDate, @CreateDate, @LastLockoutDate, @FailedPasswordAttemptCount, @FailedPasswordAttemptWindowStart, @FailedPasswordAnswerAttemptCount, @FailedPasswordAnswerAttemptWindowStart ) IF( @@ERROR<>0 ) BEGIN SET@ErrorCode=-1 GOTO Cleanup END IF( @TranStarted=1 ) BEGIN SET@TranStarted=0 COMMITTRANSACTION END RETURN0 Cleanup: IF( @TranStarted=1 ) BEGIN SET@TranStarted=0 ROLLBACKTRANSACTION END RETURN@ErrorCode END
set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTERPROCEDURE[dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer] @ApplicationNamenvarchar(256), @UserNamenvarchar(256), @NewPasswordQuestionnvarchar(256), @NewPasswordAnswernvarchar(128) AS BEGIN DECLARE@UserIduniqueidentifier SELECT@UserId=NULL SELECT@UserId= u.UserId FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a WHERE LoweredUserName =LOWER(@UserName) AND u.ApplicationId = a.ApplicationId AND LOWER(@ApplicationName) = a.LoweredApplicationName AND u.UserId = m.UserId IF (@UserIdISNULL) BEGIN RETURN(1) END UPDATE dbo.aspnet_Membership SET PasswordQuestion =@NewPasswordQuestion, PasswordAnswer =@NewPasswordAnswer WHERE UserId=@UserId RETURN(0) END
set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTERPROCEDURE[dbo].[aspnet_Membership_UpdateUser] @ApplicationNamenvarchar(256), @UserNamenvarchar(256), @Emailnvarchar(256), @Commentntext, @IsApprovedbit, @LastLoginDatedatetime, @LastActivityDatedatetime, @UniqueEmailint, @CurrentTimeUtcdatetime AS BEGIN DECLARE@UserIduniqueidentifier DECLARE@ApplicationIduniqueidentifier SELECT@UserId=NULL SELECT@UserId= u.UserId, @ApplicationId= a.ApplicationId FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m WHERE LoweredUserName =LOWER(@UserName) AND u.ApplicationId = a.ApplicationId AND LOWER(@ApplicationName) = a.LoweredApplicationName AND u.UserId = m.UserId IF (@UserIdISNULL) RETURN(1) IF (@UniqueEmail=1) BEGIN IF (EXISTS (SELECT* FROM dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK) WHERE ApplicationId =@ApplicationIdAND@UserId<> UserId AND LoweredEmail =LOWER(@Email))) BEGIN RETURN(7) END END DECLARE@TranStartedbit SET@TranStarted=0 IF( @@TRANCOUNT=0 ) BEGIN BEGINTRANSACTION SET@TranStarted=1 END ELSE SET@TranStarted=0 UPDATE dbo.aspnet_Users WITH (ROWLOCK) SET LastActivityDate =@LastActivityDate WHERE @UserId= UserId IF( @@ERROR<>0 ) GOTO Cleanup UPDATE dbo.aspnet_Membership WITH (ROWLOCK) SET Email =@Email, LoweredEmail =LOWER(@Email), Comment =@Comment, IsApproved =@IsApproved, LastLoginDate =@LastLoginDate WHERE @UserId= UserId IF( @@ERROR<>0 ) GOTO Cleanup IF( @TranStarted=1 ) BEGIN SET@TranStarted=0 COMMITTRANSACTION END RETURN0 Cleanup: IF( @TranStarted=1 ) BEGIN SET@TranStarted=0 ROLLBACKTRANSACTION END RETURN-1 END
set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTERPROCEDURE[dbo].[aspnet_Membership_GetAllUsers] @ApplicationNamenvarchar(256), @PageIndexint, @PageSizeint AS BEGIN DECLARE@ApplicationIduniqueidentifier SELECT@ApplicationId=NULL SELECT@ApplicationId= ApplicationId FROM dbo.aspnet_Applications WHERELOWER(@ApplicationName) = LoweredApplicationName IF (@ApplicationIdISNULL) RETURN0 -- Set the page bounds DECLARE@PageLowerBoundint DECLARE@PageUpperBoundint DECLARE@TotalRecordsint SET@PageLowerBound=@PageSize*@PageIndex SET@PageUpperBound=@PageSize-1+@PageLowerBound -- Create a temp table TO store the select results CREATETABLE #PageIndexForUsers ( IndexId intIDENTITY (0, 1) NOTNULL, UserId uniqueidentifier ) -- Insert into our temp table INSERTINTO #PageIndexForUsers (UserId) SELECT u.UserId FROM dbo.aspnet_Membership m, dbo.aspnet_Users u WHERE u.ApplicationId =@ApplicationIdAND u.UserId = m.UserId ORDERBY u.UserName SELECT@TotalRecords=@@ROWCOUNT SELECT u.UserName, 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_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND p.IndexId >=@PageLowerBoundAND p.IndexId <=@PageUpperBound ORDERBY u.UserName RETURN@TotalRecords END