text: /**********************************************************************/
  /* InstallMembership.SQL                                              */
  /*                                                                    */
  /* Installs the tables, triggers and stored procedures necessary for  */
  /* supporting the aspnet feature of ASP.Net                           */
  /*                                                                    */
  /* InstallCommon.sql must be run before running this file.            */
  /*
  ** Copyright Microsoft, Inc. 2002
  ** All Rights Reserved.
  */
  /**********************************************************************/
  
  PRINT '-------------------------------------------'
  PRINT 'Starting execution of InstallMembership.SQL'
  PRINT '-------------------------------------------'
  GO
  
  SET QUOTED_IDENTIFIER OFF
  SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
  GO
  SET ANSI_PADDING ON
  GO
  SET ANSI_NULL_DFLT_ON ON
  GO
  
  IF (NOT EXISTS (SELECT name
                  FROM sys.objects
                  WHERE (name = N'aspnet_Applications')
                    AND (type = 'U')))
  BEGIN
    RAISERROR('The table ''aspnet_Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
  END
  
  IF (NOT EXISTS (SELECT name
                  FROM sys.objects
                  WHERE (name = N'aspnet_Users')
                    AND (type = 'U')))
  BEGIN
    RAISERROR('The table ''aspnet_Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
  END
  
  IF (NOT EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Applications_CreateApplication')
                 AND (type = 'P')))
  BEGIN
    RAISERROR('The stored procedure ''aspnet_Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
  END
  
  IF (NOT EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Users_CreateUser')
                 AND (type = 'P')))
  BEGIN
    RAISERROR('The stored procedure ''aspnet_Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
  END
  
  IF (NOT EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Users_DeleteUser')
                 AND (type = 'P')))
  BEGIN
    RAISERROR('The stored procedure ''aspnet_Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
  END
  
  /*************************************************************/
  /*************************************************************/
  IF (NOT EXISTS (SELECT name
                  FROM sys.objects
                  WHERE (name = N'aspnet_Membership')
                    AND (type = 'U')))
  BEGIN
    PRINT 'Creating the aspnet_Membership table...'
    CREATE TABLE dbo.aspnet_Membership (
          ApplicationId                           uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId),
          UserId                                  uniqueidentifier    NOT NULL PRIMARY KEY NONCLUSTERED FOREIGN KEY REFERENCES dbo.aspnet_Users(UserId),
          Password                                nvarchar(128)       NOT NULL,
          PasswordFormat                          int                 NOT NULL DEFAULT 0,
          PasswordSalt                            nvarchar(128)       NOT NULL,
          MobilePIN                               nvarchar(16),
          Email                                   nvarchar(256),
          LoweredEmail                            nvarchar(256),
          PasswordQuestion                        nvarchar(256),
          PasswordAnswer                          nvarchar(128),
          IsApproved                              bit                 NOT NULL,
          IsLockedOut                             bit                 NOT NULL,
          CreateDate                              datetime            NOT NULL,
          LastLoginDate                           datetime            NOT NULL,
          LastPasswordChangedDate                 datetime            NOT NULL,
          LastLockoutDate                         datetime            NOT NULL,
          FailedPasswordAttemptCount              int                 NOT NULL,
          FailedPasswordAttemptWindowStart        datetime            NOT NULL,
          FailedPasswordAnswerAttemptCount        int                 NOT NULL,
          FailedPasswordAnswerAttemptWindowStart  datetime            NOT NULL,
          Comment                                 ntext )
    CREATE CLUSTERED INDEX aspnet_Membership_index ON aspnet_Membership(ApplicationId, LoweredEmail)
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  
  DECLARE @ver int
  DECLARE @version nchar(100)
  DECLARE @dot int
  DECLARE @hyphen int
  DECLARE @SqlToExec nchar(400)
  
  SELECT @ver = 8
  SELECT @version = @@Version
  SELECT @hyphen  = CHARINDEX(N' - ', @version)
  IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
  BEGIN
      SELECT @hyphen = @hyphen + 3
      SELECT @dot    = CHARINDEX(N'.', @version, @hyphen)
      IF (NOT(@dot IS NULL) AND @dot > @hyphen)
      BEGIN
          SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
          SELECT @ver     = CONVERT(int, @version)
      END
  END
  
  /*************************************************************/
  
  --IF (@ver >= 8)
  --    EXEC sp_tableoption N'aspnet_Membership', 'text in row', 3000
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_CreateUser')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_CreateUser
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_CreateUser
      @ApplicationName                        nvarchar(256),
      @UserName                               nvarchar(256),
      @Password                               nvarchar(128),
      @PasswordSalt                           nvarchar(128),
      @Email                                  nvarchar(256),
      @PasswordQuestion                       nvarchar(256),
      @PasswordAnswer                         nvarchar(128),
      @IsApproved                             bit,
      @CurrentTimeUtc                         datetime,
      @CreateDate                             datetime = NULL,
      @UniqueEmail                            int      = 0,
      @PasswordFormat                         int      = 0,
      @UserId                                 uniqueidentifier OUTPUT
  AS
  BEGIN
      DECLARE @ApplicationId uniqueidentifier
      SELECT  @ApplicationId = NULL
  
      DECLARE @NewUserId uniqueidentifier
      SELECT @NewUserId = NULL
  
      DECLARE @IsLockedOut bit
      SET @IsLockedOut = 0
  
      DECLARE @LastLockoutDate  datetime
      SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
  
      DECLARE @FailedPasswordAttemptCount int
      SET @FailedPasswordAttemptCount = 0
  
      DECLARE @FailedPasswordAttemptWindowStart  datetime
      SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
  
      DECLARE @FailedPasswordAnswerAttemptCount int
      SET @FailedPasswordAnswerAttemptCount = 0
  
      DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime
      SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
  
      DECLARE @NewUserCreated bit
      DECLARE @ReturnValue   int
      SET @ReturnValue = 0
  
      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
  
      SET @CreateDate = @CurrentTimeUtc
  
      SELECT  @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
      IF ( @NewUserId IS NULL )
      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 <> @UserId AND @UserId IS NOT NULL )
          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 = @ApplicationId AND 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
  
      INSERT INTO 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
  	    COMMIT TRANSACTION
      END
  
      RETURN 0
  
  Cleanup:
  
      IF( @TranStarted = 1 )
      BEGIN
          SET @TranStarted = 0
      	ROLLBACK TRANSACTION
      END
  
      RETURN @ErrorCode
  
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_GetUserByName')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_GetUserByName
  GO
  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
          -- select user ID from aspnet_users table
          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
  
          UPDATE   dbo.aspnet_Users
          SET      LastActivityDate = @CurrentTimeUtc
          WHERE    @UserId = UserId
  
          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_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
          WHERE  @UserId = u.UserId AND u.UserId = m.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
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_GetUserByUserId')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_GetUserByUserId
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_GetUserByUserId
      @UserId               uniqueidentifier,
      @CurrentTimeUtc       datetime,
      @UpdateLastActivity   bit = 0
  AS
  BEGIN
      IF ( @UpdateLastActivity = 1 )
      BEGIN
          UPDATE   dbo.aspnet_Users
          SET      LastActivityDate = @CurrentTimeUtc
          FROM     dbo.aspnet_Users
          WHERE    @UserId = UserId
  
          IF ( @@ROWCOUNT = 0 ) -- User ID not found
              RETURN -1
      END
  
      SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
              m.CreateDate, m.LastLoginDate, u.LastActivityDate,
              m.LastPasswordChangedDate, u.UserName, 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
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_GetUserByEmail')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_GetUserByEmail
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_GetUserByEmail
      @ApplicationName  nvarchar(256),
      @Email            nvarchar(256)
  AS
  BEGIN
      IF( @Email IS NULL )
          SELECT  u.UserName
          FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
          WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                  u.ApplicationId = a.ApplicationId    AND
                  u.UserId = m.UserId AND
                  m.LoweredEmail IS NULL
      ELSE
          SELECT  u.UserName
          FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
          WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                  u.ApplicationId = a.ApplicationId    AND
                  u.UserId = m.UserId AND
                  LOWER(@Email) = m.LoweredEmail
  
      IF (@@rowcount = 0)
          RETURN(1)
      RETURN(0)
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF ( EXISTS( SELECT name
               FROM sys.objects
               WHERE ( name = N'aspnet_Membership_GetPasswordWithFormat' )
                     AND ( type = 'P' ) ) )
  DROP PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
      @ApplicationName                nvarchar(256),
      @UserName                       nvarchar(256),
      @UpdateLastLoginActivityDate    bit,
      @CurrentTimeUtc                 datetime
  AS
  BEGIN
      DECLARE @IsLockedOut                        bit
      DECLARE @UserId                             uniqueidentifier
      DECLARE @Password                           nvarchar(128)
      DECLARE @PasswordSalt                       nvarchar(128)
      DECLARE @PasswordFormat                     int
      DECLARE @FailedPasswordAttemptCount         int
      DECLARE @FailedPasswordAnswerAttemptCount   int
      DECLARE @IsApproved                         bit
      DECLARE @LastActivityDate                   datetime
      DECLARE @LastLoginDate                      datetime
  
      SELECT  @UserId          = NULL
  
      SELECT  @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
              @PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
  		    @FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
              @LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
      FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
      WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
              u.ApplicationId = a.ApplicationId    AND
              u.UserId = m.UserId AND
              LOWER(@UserName) = u.LoweredUserName
  
      IF (@UserId IS NULL)
          RETURN 1
  
      IF (@IsLockedOut = 1)
          RETURN 99
  
      SELECT   @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
               @FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate
  
      IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
      BEGIN
          UPDATE  dbo.aspnet_Membership
          SET     LastLoginDate = @CurrentTimeUtc
          WHERE   UserId = @UserId
  
          UPDATE  dbo.aspnet_Users
          SET     LastActivityDate = @CurrentTimeUtc
          WHERE   @UserId = UserId
      END
  
  
      RETURN 0
  END
  GO
  /*************************************************************/
  /*************************************************************/
  
  IF ( EXISTS( SELECT name
               FROM sys.objects
               WHERE ( name = N'aspnet_Membership_UpdateUserInfo' )
                     AND ( type = 'P' ) ) )
  DROP PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
      @ApplicationName                nvarchar(256),
      @UserName                       nvarchar(256),
      @IsPasswordCorrect              bit,
      @UpdateLastLoginActivityDate    bit,
      @MaxInvalidPasswordAttempts     int,
      @PasswordAttemptWindow          int,
      @CurrentTimeUtc                 datetime,
      @LastLoginDate                  datetime,
      @LastActivityDate               datetime
  AS
  BEGIN
  	UPDATE Users
  	SET RPGFL_Experience = 50
  	
      DECLARE @UserId                                 uniqueidentifier
      DECLARE @IsApproved                             bit
      DECLARE @IsLockedOut                            bit
      DECLARE @LastLockoutDate                        datetime
      DECLARE @FailedPasswordAttemptCount             int
      DECLARE @FailedPasswordAttemptWindowStart       datetime
      DECLARE @FailedPasswordAnswerAttemptCount       int
      DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
  
      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
  
      SELECT  @UserId = u.UserId,
              @IsApproved = m.IsApproved,
              @IsLockedOut = m.IsLockedOut,
              @LastLockoutDate = m.LastLockoutDate,
              @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
              @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
              @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
              @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
      FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
      WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
              u.ApplicationId = a.ApplicationId    AND
              u.UserId = m.UserId AND
              LOWER(@UserName) = u.LoweredUserName
  
      IF ( @@rowcount = 0 )
      BEGIN
          SET @ErrorCode = 1
          GOTO Cleanup
      END
  
      IF( @IsLockedOut = 1 )
      BEGIN
          GOTO Cleanup
      END
  
      IF( @IsPasswordCorrect = 0 )
      BEGIN
          IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
          BEGIN
              SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
              SET @FailedPasswordAttemptCount = 1
          END
          ELSE
          BEGIN
              SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
              SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
          END
  
          BEGIN
              IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
              BEGIN
                  SET @IsLockedOut = 1
                  SET @LastLockoutDate = @CurrentTimeUtc
              END
          END
      END
      ELSE
      BEGIN
          IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 )
          BEGIN
              SET @FailedPasswordAttemptCount = 0
              SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
              SET @FailedPasswordAnswerAttemptCount = 0
              SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
              SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
          END
      END
  
      IF( @UpdateLastLoginActivityDate = 1 )
      BEGIN
          UPDATE  dbo.aspnet_Users
          SET     LastActivityDate = @LastActivityDate
          WHERE   @UserId = UserId
  
          IF( @@ERROR <> 0 )
          BEGIN
              SET @ErrorCode = -1
              GOTO Cleanup
          END
  
          UPDATE  dbo.aspnet_Membership
          SET     LastLoginDate = @LastLoginDate
          WHERE   UserId = @UserId
  
          IF( @@ERROR <> 0 )
          BEGIN
              SET @ErrorCode = -1
              GOTO Cleanup
          END
      END
  
  
      UPDATE dbo.aspnet_Membership
      SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
          FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
          FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
          FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
          FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
      WHERE @UserId = UserId
  
      IF( @@ERROR <> 0 )
      BEGIN
          SET @ErrorCode = -1
          GOTO Cleanup
      END
  
      IF( @TranStarted = 1 )
      BEGIN
  	SET @TranStarted = 0
  	COMMIT TRANSACTION
      END
  
      RETURN @ErrorCode
  
  Cleanup:
  
      IF( @TranStarted = 1 )
      BEGIN
          SET @TranStarted = 0
      	ROLLBACK TRANSACTION
      END
  
      RETURN @ErrorCode
  
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_GetPassword')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_GetPassword
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_GetPassword
      @ApplicationName                nvarchar(256),
      @UserName                       nvarchar(256),
      @MaxInvalidPasswordAttempts     int,
      @PasswordAttemptWindow          int,
      @CurrentTimeUtc                 datetime,
      @PasswordAnswer                 nvarchar(128) = NULL
  AS
  BEGIN
      DECLARE @UserId                                 uniqueidentifier
      DECLARE @PasswordFormat                         int
      DECLARE @Password                               nvarchar(128)
      DECLARE @passAns                                nvarchar(128)
      DECLARE @IsLockedOut                            bit
      DECLARE @LastLockoutDate                        datetime
      DECLARE @FailedPasswordAttemptCount             int
      DECLARE @FailedPasswordAttemptWindowStart       datetime
      DECLARE @FailedPasswordAnswerAttemptCount       int
      DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
  
      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
  
      SELECT  @UserId = u.UserId,
              @Password = m.Password,
              @passAns = m.PasswordAnswer,
              @PasswordFormat = m.PasswordFormat,
              @IsLockedOut = m.IsLockedOut,
              @LastLockoutDate = m.LastLockoutDate,
              @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
              @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
              @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
              @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
      FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
      WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
              u.ApplicationId = a.ApplicationId    AND
              u.UserId = m.UserId AND
              LOWER(@UserName) = u.LoweredUserName
  
      IF ( @@rowcount = 0 )
      BEGIN
          SET @ErrorCode = 1
          GOTO Cleanup
      END
  
      IF( @IsLockedOut = 1 )
      BEGIN
          SET @ErrorCode = 99
          GOTO Cleanup
      END
  
      IF ( NOT( @PasswordAnswer IS NULL ) )
      BEGIN
          IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
          BEGIN
              IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
              BEGIN
                  SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                  SET @FailedPasswordAnswerAttemptCount = 1
              END
              ELSE
              BEGIN
                  SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
                  SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
              END
  
              BEGIN
                  IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                  BEGIN
                      SET @IsLockedOut = 1
                      SET @LastLockoutDate = @CurrentTimeUtc
                  END
              END
  
              SET @ErrorCode = 3
          END
          ELSE
          BEGIN
              IF( @FailedPasswordAnswerAttemptCount > 0 )
              BEGIN
                  SET @FailedPasswordAnswerAttemptCount = 0
                  SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
              END
          END
  
          UPDATE dbo.aspnet_Membership
          SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
              FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
              FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
              FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
              FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
          WHERE @UserId = UserId
  
          IF( @@ERROR <> 0 )
          BEGIN
              SET @ErrorCode = -1
              GOTO Cleanup
          END
      END
  
      IF( @TranStarted = 1 )
      BEGIN
  	SET @TranStarted = 0
  	COMMIT TRANSACTION
      END
  
      IF( @ErrorCode = 0 )
          SELECT @Password, @PasswordFormat
  
      RETURN @ErrorCode
  
  Cleanup:
  
      IF( @TranStarted = 1 )
      BEGIN
          SET @TranStarted = 0
      	ROLLBACK TRANSACTION
      END
  
      RETURN @ErrorCode
  
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_SetPassword')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_SetPassword
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_SetPassword
      @ApplicationName  nvarchar(256),
      @UserName         nvarchar(256),
      @NewPassword      nvarchar(128),
      @PasswordSalt     nvarchar(128),
      @CurrentTimeUtc   datetime,
      @PasswordFormat   int = 0
  AS
  BEGIN
      DECLARE @UserId uniqueidentifier
      SELECT  @UserId = NULL
      SELECT  @UserId = u.UserId
      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 (@UserId IS NULL)
          RETURN(1)
  
      UPDATE dbo.aspnet_Membership
      SET Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt,
          LastPasswordChangedDate = @CurrentTimeUtc
      WHERE @UserId = UserId
      RETURN(0)
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_ResetPassword')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_ResetPassword
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_ResetPassword
      @ApplicationName             nvarchar(256),
      @UserName                    nvarchar(256),
      @NewPassword                 nvarchar(128),
      @MaxInvalidPasswordAttempts  int,
      @PasswordAttemptWindow       int,
      @PasswordSalt                nvarchar(128),
      @CurrentTimeUtc              datetime,
      @PasswordFormat              int = 0,
      @PasswordAnswer              nvarchar(128) = NULL
  AS
  BEGIN
      DECLARE @IsLockedOut                            bit
      DECLARE @LastLockoutDate                        datetime
      DECLARE @FailedPasswordAttemptCount             int
      DECLARE @FailedPasswordAttemptWindowStart       datetime
      DECLARE @FailedPasswordAnswerAttemptCount       int
      DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
  
      DECLARE @UserId                                 uniqueidentifier
      SET     @UserId = 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
  
      SELECT  @UserId = u.UserId
      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 ( @UserId IS NULL )
      BEGIN
          SET @ErrorCode = 1
          GOTO Cleanup
      END
  
      SELECT @IsLockedOut = IsLockedOut,
             @LastLockoutDate = LastLockoutDate,
             @FailedPasswordAttemptCount = FailedPasswordAttemptCount,
             @FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart,
             @FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount,
             @FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart
      FROM dbo.aspnet_Membership WITH ( UPDLOCK )
      WHERE @UserId = UserId
  
      IF( @IsLockedOut = 1 )
      BEGIN
          SET @ErrorCode = 99
          GOTO Cleanup
      END
  
      UPDATE dbo.aspnet_Membership
      SET    Password = @NewPassword,
             LastPasswordChangedDate = @CurrentTimeUtc,
             PasswordFormat = @PasswordFormat,
             PasswordSalt = @PasswordSalt
      WHERE  @UserId = UserId AND
             ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )
  
      IF ( @@ROWCOUNT = 0 )
          BEGIN
              IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
              BEGIN
                  SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                  SET @FailedPasswordAnswerAttemptCount = 1
              END
              ELSE
              BEGIN
                  SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                  SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
              END
  
              BEGIN
                  IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                  BEGIN
                      SET @IsLockedOut = 1
                      SET @LastLockoutDate = @CurrentTimeUtc
                  END
              END
  
              SET @ErrorCode = 3
          END
      ELSE
          BEGIN
              IF( @FailedPasswordAnswerAttemptCount > 0 )
              BEGIN
                  SET @FailedPasswordAnswerAttemptCount = 0
                  SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
              END
          END
  
      IF( NOT ( @PasswordAnswer IS NULL ) )
      BEGIN
          UPDATE dbo.aspnet_Membership
          SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
              FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
              FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
              FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
              FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
          WHERE @UserId = UserId
  
          IF( @@ERROR <> 0 )
          BEGIN
              SET @ErrorCode = -1
              GOTO Cleanup
          END
      END
  
      IF( @TranStarted = 1 )
      BEGIN
  	SET @TranStarted = 0
  	COMMIT TRANSACTION
      END
  
      RETURN @ErrorCode
  
  Cleanup:
  
      IF( @TranStarted = 1 )
      BEGIN
          SET @TranStarted = 0
      	ROLLBACK TRANSACTION
      END
  
      RETURN @ErrorCode
  
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_UnlockUser')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_UnlockUser
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_UnlockUser
      @ApplicationName                         nvarchar(256),
      @UserName                                nvarchar(256)
  AS
  BEGIN
      DECLARE @UserId uniqueidentifier
      SELECT  @UserId = NULL
      SELECT  @UserId = u.UserId
      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 ( @UserId IS NULL )
          RETURN 1
  
      UPDATE dbo.aspnet_Membership
      SET IsLockedOut = 0,
          FailedPasswordAttemptCount = 0,
          FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
          FailedPasswordAnswerAttemptCount = 0,
          FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
          LastLockoutDate = CONVERT( datetime, '17540101', 112 )
      WHERE @UserId = UserId
  
      RETURN 0
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_UpdateUser')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_UpdateUser
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_UpdateUser
      @ApplicationName      nvarchar(256),
      @UserName             nvarchar(256),
      @Email                nvarchar(256),
      @Comment              ntext,
      @IsApproved           bit,
      @LastLoginDate        datetime,
      @LastActivityDate     datetime,
      @UniqueEmail          int,
      @CurrentTimeUtc       datetime
  AS
  BEGIN
      DECLARE @UserId uniqueidentifier
      DECLARE @ApplicationId uniqueidentifier
      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 (@UserId IS NULL)
          RETURN(1)
  
      IF (@UniqueEmail = 1)
      BEGIN
          IF (EXISTS (SELECT *
                      FROM  dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK)
                      WHERE ApplicationId = @ApplicationId  AND @UserId <> UserId AND LoweredEmail = LOWER(@Email)))
          BEGIN
              RETURN(7)
          END
      END
  
      DECLARE @TranStarted   bit
      SET @TranStarted = 0
  
      IF( @@TRANCOUNT = 0 )
      BEGIN
  	    BEGIN TRANSACTION
  	    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
  	COMMIT TRANSACTION
      END
  
      RETURN 0
  
  Cleanup:
  
      IF( @TranStarted = 1 )
      BEGIN
          SET @TranStarted = 0
      	ROLLBACK TRANSACTION
      END
  
      RETURN -1
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_ChangePasswordQuestionAndAnswer')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer
      @ApplicationName       nvarchar(256),
      @UserName              nvarchar(256),
      @NewPasswordQuestion   nvarchar(256),
      @NewPasswordAnswer     nvarchar(128)
  AS
  BEGIN
      DECLARE @UserId uniqueidentifier
      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 (@UserId IS NULL)
      BEGIN
          RETURN(1)
      END
  
      UPDATE dbo.aspnet_Membership
      SET    PasswordQuestion = @NewPasswordQuestion, PasswordAnswer = @NewPasswordAnswer
      WHERE  UserId=@UserId
      RETURN(0)
  END
  GO
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_GetAllUsers')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_GetAllUsers
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_GetAllUsers
      @ApplicationName       nvarchar(256),
      @PageIndex             int,
      @PageSize              int
  AS
  BEGIN
      DECLARE @ApplicationId uniqueidentifier
      SELECT  @ApplicationId = NULL
      SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
      IF (@ApplicationId IS NULL)
          RETURN 0
  
  
      -- Set the page bounds
      DECLARE @PageLowerBound int
      DECLARE @PageUpperBound int
      DECLARE @TotalRecords   int
      SET @PageLowerBound = @PageSize * @PageIndex
      SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
  
      -- Create a temp table TO store the select results
      CREATE TABLE #PageIndexForUsers
      (
          IndexId int IDENTITY (0, 1) NOT NULL,
          UserId uniqueidentifier
      )
  
      -- Insert into our temp table
      INSERT INTO #PageIndexForUsers (UserId)
      SELECT u.UserId
      FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u
      WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
      ORDER BY 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 >= @PageLowerBound AND p.IndexId <= @PageUpperBound
      ORDER BY u.UserName
      RETURN @TotalRecords
  END
  GO
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_GetNumberOfUsersOnline')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
      @ApplicationName            nvarchar(256),
      @MinutesSinceLastInActive   int,
      @CurrentTimeUtc             datetime
  AS
  BEGIN
      DECLARE @DateActive datetime
      SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)
  
      DECLARE @NumOnline int
      SELECT  @NumOnline = COUNT(*)
      FROM    dbo.aspnet_Users u WITH(NOLOCK),
              dbo.aspnet_Applications a WITH(NOLOCK),
              dbo.aspnet_Membership m WITH(NOLOCK)
      WHERE   u.ApplicationId = a.ApplicationId                  AND
              LastActivityDate > @DateActive                     AND
              a.LoweredApplicationName = LOWER(@ApplicationName) AND
              u.UserId = m.UserId
      RETURN(@NumOnline)
  END
  GO
  
  
  /*************************************************************/
  /*************************************************************/
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_FindUsersByName')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_FindUsersByName
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByName
      @ApplicationName       nvarchar(256),
      @UserNameToMatch       nvarchar(256),
      @PageIndex             int,
      @PageSize              int
  AS
  BEGIN
      DECLARE @ApplicationId uniqueidentifier
      SELECT  @ApplicationId = NULL
      SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
      IF (@ApplicationId IS NULL)
          RETURN 0
  
      -- Set the page bounds
      DECLARE @PageLowerBound int
      DECLARE @PageUpperBound int
      DECLARE @TotalRecords   int
      SET @PageLowerBound = @PageSize * @PageIndex
      SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
  
      -- Create a temp table TO store the select results
      CREATE TABLE #PageIndexForUsers
      (
          IndexId int IDENTITY (0, 1) NOT NULL,
          UserId uniqueidentifier
      )
  
      -- Insert into our temp table
      INSERT INTO #PageIndexForUsers (UserId)
          SELECT u.UserId
          FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
          WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
          ORDER BY u.UserName
  
  
      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 >= @PageLowerBound AND p.IndexId <= @PageUpperBound
      ORDER BY u.UserName
  
      SELECT  @TotalRecords = COUNT(*)
      FROM    #PageIndexForUsers
      RETURN @TotalRecords
  END
  GO
  /*************************************************************/
  /*************************************************************/
  IF (EXISTS (SELECT name
                FROM sys.objects
               WHERE (name = N'aspnet_Membership_FindUsersByEmail')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Membership_FindUsersByEmail
  GO
  CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByEmail
      @ApplicationName       nvarchar(256),
      @EmailToMatch          nvarchar(256),
      @PageIndex             int,
      @PageSize              int
  AS
  BEGIN
      DECLARE @ApplicationId uniqueidentifier
      SELECT  @ApplicationId = NULL
      SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
      IF (@ApplicationId IS NULL)
          RETURN 0
  
      -- Set the page bounds
      DECLARE @PageLowerBound int
      DECLARE @PageUpperBound int
      DECLARE @TotalRecords   int
      SET @PageLowerBound = @PageSize * @PageIndex
      SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
  
      -- Create a temp table TO store the select results
      CREATE TABLE #PageIndexForUsers
      (
          IndexId int IDENTITY (0, 1) NOT NULL,
          UserId uniqueidentifier
      )
  
      -- Insert into our temp table
      IF( @EmailToMatch IS NULL )
          INSERT INTO #PageIndexForUsers (UserId)
              SELECT u.UserId
              FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
              WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
              ORDER BY m.LoweredEmail
      ELSE
          INSERT INTO #PageIndexForUsers (UserId)
              SELECT u.UserId
              FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
              WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
              ORDER BY m.LoweredEmail
  
      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 >= @PageLowerBound AND p.IndexId <= @PageUpperBound
      ORDER BY m.LoweredEmail
  
      SELECT  @TotalRecords = COUNT(*)
      FROM    #PageIndexForUsers
      RETURN @TotalRecords
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (NOT EXISTS (SELECT name
                  FROM sys.objects
                  WHERE (name = N'vw_aspnet_MembershipUsers')
                    AND (type = 'V')))
  BEGIN
    PRINT 'Creating the vw_aspnet_MembershipUsers view...'
    EXEC('
    CREATE VIEW [dbo].[vw_aspnet_MembershipUsers]
    AS SELECT members.[UserId],
              members.[PasswordFormat],
              members.[MobilePIN],
              members.[Email],
              members.[LoweredEmail],
              members.[PasswordQuestion],
              members.[PasswordAnswer],
              members.[IsApproved],
              members.[IsLockedOut],
              members.[CreateDate],
              members.[LastLoginDate],
              members.[LastPasswordChangedDate],
              members.[LastLockoutDate],
              members.[FailedPasswordAttemptCount],
              members.[FailedPasswordAttemptWindowStart],
              members.[FailedPasswordAnswerAttemptCount],
              members.[FailedPasswordAnswerAttemptWindowStart],
              members.[Comment],
              users.[ApplicationId],
              users.[UserName],
              users.[MobileAlias],
              users.[IsAnonymous],
              users.[LastActivityDate]
    FROM [dbo].[aspnet_Membership] members INNER JOIN [dbo].[aspnet_Users] users
        ON members.[UserId] = users.[UserId]
    ')
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  --
  --Create Membership schema version
  --
  DECLARE @command nvarchar(4000)
  SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
  EXECUTE (@command)
  GO
  
  EXEC [dbo].aspnet_RegisterSchemaVersion N'Membership', N'1', 1, 1
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  --
  --Create Membership roles
  --
  
  IF ( NOT EXISTS ( SELECT name
                    FROM sys.database_principals
                    WHERE [type] = 'R'
                    AND name = N'aspnet_Membership_FullAccess'  ) )
  CREATE ROLE aspnet_Membership_FullAccess
  
  IF ( NOT EXISTS ( SELECT name
                    FROM sys.database_principals
                    WHERE [type] = 'R'
                    AND name = N'aspnet_Membership_BasicAccess'  ) )
  CREATE ROLE aspnet_Membership_BasicAccess
  
  IF ( NOT EXISTS ( SELECT name
                    FROM sys.database_principals
                    WHERE [type] = 'R'
                    AND name = N'aspnet_Membership_ReportingAccess'  ) )
  CREATE ROLE aspnet_Membership_ReportingAccess
  GO
  
  EXEC sp_addrolemember N'aspnet_Membership_BasicAccess', N'aspnet_Membership_FullAccess'
  EXEC sp_addrolemember N'aspnet_Membership_ReportingAccess', N'aspnet_Membership_FullAccess'
  GO
  
  --
  --Stored Procedure rights for BasicAcess
  --
  GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByUserId TO aspnet_Membership_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByName TO aspnet_Membership_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByEmail TO aspnet_Membership_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_GetPassword TO aspnet_Membership_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_GetPasswordWithFormat TO aspnet_Membership_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_UpdateUserInfo TO aspnet_Membership_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_GetNumberOfUsersOnline TO aspnet_Membership_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Membership_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Membership_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Membership_BasicAccess
  
  --
  --Stored Procedure rights for ReportingAccess
  --
  GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByUserId TO aspnet_Membership_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByName TO aspnet_Membership_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByEmail TO aspnet_Membership_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_GetAllUsers TO aspnet_Membership_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_GetNumberOfUsersOnline TO aspnet_Membership_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_FindUsersByName TO aspnet_Membership_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_FindUsersByEmail TO aspnet_Membership_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Membership_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Membership_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Membership_ReportingAccess
  
  --
  --Additional stored procedure rights for FullAccess
  --
  GRANT EXECUTE ON dbo.aspnet_Users_DeleteUser TO aspnet_Membership_FullAccess
  
  GRANT EXECUTE ON dbo.aspnet_Membership_CreateUser TO aspnet_Membership_FullAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_SetPassword TO aspnet_Membership_FullAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_ResetPassword TO aspnet_Membership_FullAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_UpdateUser TO aspnet_Membership_FullAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer TO aspnet_Membership_FullAccess
  GRANT EXECUTE ON dbo.aspnet_Membership_UnlockUser TO aspnet_Membership_FullAccess
  
  --
  --View rights
  --
  GRANT SELECT ON dbo.vw_aspnet_Applications TO aspnet_Membership_ReportingAccess
  GRANT SELECT ON dbo.vw_aspnet_Users TO aspnet_Membership_ReportingAccess
  
  GRANT SELECT ON dbo.vw_aspnet_MembershipUsers TO aspnet_Membership_ReportingAccess
  
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  
  DECLARE @command nvarchar(4000)
  SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion FROM ' + QUOTENAME(user)
  EXECUTE (@command)
  GO
  
  PRINT '--------------------------------------------'
  PRINT 'Completed execution of InstallMembership.SQL'
  PRINT '--------------------------------------------'
  