text: ﻿/**********************************************************************/
  /* InstallCommon.SQL                                                  */
  /*                                                                    */
  /* Installs the tables, triggers and stored procedures necessary for  */
  /* supporting some features of ASP.Net                                */
  /*
  ** Copyright Microsoft, Inc. 2003
  ** All Rights Reserved.
  */
  /**********************************************************************/
  
  PRINT '---------------------------------------'
  PRINT 'Starting execution of InstallCommon.SQL'
  PRINT '---------------------------------------'
  GO
  
  SET QUOTED_IDENTIFIER OFF
  SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
  GO
  
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  -- Create the temporary permission tables and stored procedures
  -- TO preserve the permissions of an object.
  --
  -- We use this method instead of using CREATE (if the object
  -- doesn't exist) and ALTER (if the object exists) because the
  -- latter one either requires the use of dynamic SQL (which we want to
  -- avoid) or writing the body of the object (e.g. an SP or view) twice,
  -- once use CREATE and again using ALTER.
  
  
  IF (OBJECT_ID('tempdb.#aspnet_Permissions') IS NOT NULL)
  BEGIN
      DROP TABLE #aspnet_Permissions
  END
  GO
  
  CREATE TABLE #aspnet_Permissions
  (
      Owner     sysname,
      Object    sysname,
      Grantee   sysname,
      Grantor   sysname,
      ProtectType char(10),
      [Action]    varchar(60),
      [Column]    sysname
  )
  
  INSERT INTO #aspnet_Permissions
  EXEC sp_helprotect
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_Setup_RestorePermissions')
                 AND (type = 'P')))
  DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions
  GO
  
  CREATE PROCEDURE [dbo].aspnet_Setup_RestorePermissions
      @name   sysname
  AS
  BEGIN
      DECLARE @object sysname
      DECLARE @protectType char(10)
      DECLARE @action varchar(60)
      DECLARE @grantee sysname
      DECLARE @cmd nvarchar(500)
      DECLARE c1 cursor FORWARD_ONLY FOR
          SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name
  
      OPEN c1
  
      FETCH c1 INTO @object, @protectType, @action, @grantee
      WHILE (@@fetch_status = 0)
      BEGIN
          SET @cmd = @protectType + ' ' + @action + ' on ' + @object + ' TO [' + @grantee + ']'
          EXEC (@cmd)
          FETCH c1 INTO @object, @protectType, @action, @grantee
      END
  
      CLOSE c1
      DEALLOCATE c1
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_Setup_RemoveAllRoleMembers')
                 AND (type = 'P')))
  DROP PROCEDURE [dbo].aspnet_Setup_RemoveAllRoleMembers
  GO
  
  CREATE PROCEDURE [dbo].aspnet_Setup_RemoveAllRoleMembers
      @name   sysname
  AS
  BEGIN
      CREATE TABLE #aspnet_RoleMembers
      (
          Group_name      sysname,
          Group_id        smallint,
          Users_in_group  sysname,
          User_id         smallint
      )
  
      INSERT INTO #aspnet_RoleMembers
      EXEC sp_helpuser @name
  
      DECLARE @user_id smallint
      DECLARE @cmd nvarchar(500)
      DECLARE c1 cursor FORWARD_ONLY FOR
          SELECT User_id FROM #aspnet_RoleMembers
  
      OPEN c1
  
      FETCH c1 INTO @user_id
      WHILE (@@fetch_status = 0)
      BEGIN
          SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + ''''
          EXEC (@cmd)
          FETCH c1 INTO @user_id
      END
  
      CLOSE c1
      DEALLOCATE c1
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  -- Create the aspnet_Applications table.
  
  IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                  WHERE (name = N'aspnet_Applications')
                    AND (type = 'U')))
  BEGIN
    PRINT 'Creating the aspnet_Applications table...'
    CREATE TABLE [dbo].aspnet_Applications (
      ApplicationName         nvarchar(256)               NOT NULL UNIQUE,
      LoweredApplicationName  nvarchar(256)               NOT NULL UNIQUE,
      ApplicationId           uniqueidentifier            PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
      Description             nvarchar(256)       )
    CREATE CLUSTERED INDEX aspnet_Applications_Index ON [dbo].aspnet_Applications(LoweredApplicationName)
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  -- Create the aspnet_Users table
  IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                  WHERE (name = N'aspnet_Users')
                    AND (type = 'U')))
  BEGIN
    PRINT 'Creating the aspnet_Users table...'
    CREATE TABLE [dbo].aspnet_Users (
      ApplicationId    uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES [dbo].aspnet_Applications(ApplicationId),
      UserId           uniqueidentifier    NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
      UserName         nvarchar(256)       NOT NULL,
      LoweredUserName  nvarchar(256)	     NOT NULL,
      MobileAlias      nvarchar(16)        DEFAULT NULL,
      IsAnonymous      bit                 NOT NULL DEFAULT 0,
      LastActivityDate DATETIME            NOT NULL)
  
     CREATE UNIQUE CLUSTERED INDEX aspnet_Users_Index ON [dbo].aspnet_Users(ApplicationId, LoweredUserName)
     CREATE NONCLUSTERED INDEX aspnet_Users_Index2 ON [dbo].aspnet_Users(ApplicationId, LastActivityDate)
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  -- Create the aspnet_SchemaVersions table
  IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                  WHERE (name = N'aspnet_SchemaVersions')
                    AND (type = 'U')))
  BEGIN
    PRINT 'Creating the aspnet_SchemaVersions table...'
    CREATE TABLE [dbo].aspnet_SchemaVersions (
      Feature                  nvarchar(128)  NOT NULL PRIMARY KEY CLUSTERED( Feature, CompatibleSchemaVersion ),
      CompatibleSchemaVersion  nvarchar(128)	NOT NULL,
      IsCurrentVersion         bit            NOT NULL )
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  ------------- Create Stored Procedures
  /*************************************************************/
  /*************************************************************/
  -- RegisterSchemaVersion SP
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_RegisterSchemaVersion')
                 AND (type = 'P')))
  DROP PROCEDURE [dbo].aspnet_RegisterSchemaVersion
  GO
  
  CREATE PROCEDURE [dbo].aspnet_RegisterSchemaVersion
      @Feature                   nvarchar(128),
      @CompatibleSchemaVersion   nvarchar(128),
      @IsCurrentVersion          bit,
      @RemoveIncompatibleSchema  bit
  AS
  BEGIN
      IF( @RemoveIncompatibleSchema = 1 )
      BEGIN
          DELETE FROM dbo.aspnet_SchemaVersions WHERE Feature = LOWER( @Feature )
      END
      ELSE
      BEGIN
          IF( @IsCurrentVersion = 1 )
          BEGIN
              UPDATE dbo.aspnet_SchemaVersions
              SET IsCurrentVersion = 0
              WHERE Feature = LOWER( @Feature )
          END
      END
  
      INSERT  dbo.aspnet_SchemaVersions( Feature, CompatibleSchemaVersion, IsCurrentVersion )
      VALUES( LOWER( @Feature ), @CompatibleSchemaVersion, @IsCurrentVersion )
  END
  GO
  
  DECLARE @command nvarchar(4000)
  
  SET @command = 'GRANT EXECUTE ON [dbo].aspnet_Setup_RestorePermissions TO ' + QUOTENAME(user)
  EXEC (@command)
  SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
  EXEC (@command)
  GO
  
  -- Restore the permissions
  EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_RegisterSchemaVersion'
  GO
  
  -- Create common schema version
  EXEC [dbo].aspnet_RegisterSchemaVersion N'Common', N'1', 1, 1
  GO
  
  /*************************************************************/
  /*************************************************************/
  -- CheckSchemaVersion SP
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_CheckSchemaVersion')
                 AND (type = 'P')))
  DROP PROCEDURE [dbo].aspnet_CheckSchemaVersion
  GO
  
  CREATE PROCEDURE [dbo].aspnet_CheckSchemaVersion
      @Feature                   nvarchar(128),
      @CompatibleSchemaVersion   nvarchar(128)
  AS
  BEGIN
      IF (EXISTS( SELECT  *
                  FROM    dbo.aspnet_SchemaVersions
                  WHERE   Feature = LOWER( @Feature ) AND
                          CompatibleSchemaVersion = @CompatibleSchemaVersion ))
          RETURN 0
  
      RETURN 1
  END
  GO
  
  -- Restore the permissions
  EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_CheckSchemaVersion'
  GO
  
  /*************************************************************/
  /*************************************************************/
  -- CreateApplication SP
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_Applications_CreateApplication')
                 AND (type = 'P')))
  DROP PROCEDURE [dbo].aspnet_Applications_CreateApplication
  GO
  
  CREATE PROCEDURE [dbo].aspnet_Applications_CreateApplication
      @ApplicationName      nvarchar(256),
      @ApplicationId        uniqueidentifier OUTPUT
  AS
  BEGIN
      SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
  
      IF(@ApplicationId IS NULL)
      BEGIN
          DECLARE @TranStarted   bit
          SET @TranStarted = 0
  
          IF( @@TRANCOUNT = 0 )
          BEGIN
  	        BEGIN TRANSACTION
  	        SET @TranStarted = 1
          END
          ELSE
      	    SET @TranStarted = 0
  
          SELECT  @ApplicationId = ApplicationId
          FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK)
          WHERE LOWER(@ApplicationName) = LoweredApplicationName
  
          IF(@ApplicationId IS NULL)
          BEGIN
              SELECT  @ApplicationId = NEWID()
              INSERT  dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
              VALUES  (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
          END
  
  
          IF( @TranStarted = 1 )
          BEGIN
              IF(@@ERROR = 0)
              BEGIN
  	        SET @TranStarted = 0
  	        COMMIT TRANSACTION
              END
              ELSE
              BEGIN
                  SET @TranStarted = 0
                  ROLLBACK TRANSACTION
              END
          END
      END
  END
  GO
  
  -- Restore the permissions
  EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Applications_CreateApplication'
  GO
  
  /*************************************************************/
  /*************************************************************/
  -- UnRegisterSchemaVersion SP
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_UnRegisterSchemaVersion')
                 AND (type = 'P')))
  DROP PROCEDURE [dbo].aspnet_UnRegisterSchemaVersion
  GO
  
  CREATE PROCEDURE [dbo].aspnet_UnRegisterSchemaVersion
      @Feature                   nvarchar(128),
      @CompatibleSchemaVersion   nvarchar(128)
  AS
  BEGIN
      DELETE FROM dbo.aspnet_SchemaVersions
          WHERE   Feature = LOWER(@Feature) AND @CompatibleSchemaVersion = CompatibleSchemaVersion
  END
  GO
  
  -- Restore the permissions
  EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_UnRegisterSchemaVersion'
  GO
  
  /*************************************************************/
  /*************************************************************/
  -- CreateUser SP
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_Users_CreateUser')
                 AND (type = 'P')))
  DROP PROCEDURE [dbo].aspnet_Users_CreateUser
  GO
  
  CREATE PROCEDURE [dbo].aspnet_Users_CreateUser
      @ApplicationId    uniqueidentifier,
      @UserName         nvarchar(256),
      @IsUserAnonymous  bit,
      @LastActivityDate DATETIME,
      @UserId           uniqueidentifier OUTPUT
  AS
  BEGIN
      IF( @UserId IS NULL )
          SELECT @UserId = NEWID()
      ELSE
      BEGIN
          IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
                      WHERE @UserId = UserId ) )
              RETURN -1
      END
  
      INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
      VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)
  
      RETURN 0
  END
  GO
  
  -- Restore the permissions
  EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Users_CreateUser'
  GO
  
  /*************************************************************/
  /*************************************************************/
  --- DeleteUser SP
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_Users_DeleteUser')
                 AND (type = 'P')))
  DROP PROCEDURE [dbo].aspnet_Users_DeleteUser
  GO
  CREATE PROCEDURE [dbo].aspnet_Users_DeleteUser
      @ApplicationName  nvarchar(256),
      @UserName         nvarchar(256),
      @TablesToDeleteFrom int,
      @NumTablesDeletedFrom int OUTPUT
  AS
  BEGIN
      DECLARE @UserId               uniqueidentifier
      SELECT  @UserId               = NULL
      SELECT  @NumTablesDeletedFrom = 0
  
      DECLARE @TranStarted   bit
      SET @TranStarted = 0
  
      IF( @@TRANCOUNT = 0 )
      BEGIN
  	    BEGIN TRANSACTION
  	    SET @TranStarted = 1
      END
      ELSE
  	SET @TranStarted = 0
  
      DECLARE @ErrorCode   int
      DECLARE @RowCount    int
  
      SET @ErrorCode = 0
      SET @RowCount  = 0
  
      SELECT  @UserId = u.UserId
      FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a
      WHERE   u.LoweredUserName       = LOWER(@UserName)
          AND u.ApplicationId         = a.ApplicationId
          AND LOWER(@ApplicationName) = a.LoweredApplicationName
  
      IF (@UserId IS NULL)
      BEGIN
          GOTO Cleanup
      END
  
      -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
      IF ((@TablesToDeleteFrom & 1) <> 0 AND
          (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
      BEGIN
          DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
  
          SELECT @ErrorCode = @@ERROR,
                 @RowCount = @@ROWCOUNT
  
          IF( @ErrorCode <> 0 )
              GOTO Cleanup
  
          IF (@RowCount <> 0)
              SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
      END
  
      -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
      IF ((@TablesToDeleteFrom & 2) <> 0  AND
          (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_UsersInRoles') AND (type = 'V'))) )
      BEGIN
          DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
  
          SELECT @ErrorCode = @@ERROR,
                  @RowCount = @@ROWCOUNT
  
          IF( @ErrorCode <> 0 )
              GOTO Cleanup
  
          IF (@RowCount <> 0)
              SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
      END
  
      -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
      IF ((@TablesToDeleteFrom & 4) <> 0  AND
          (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
      BEGIN
          DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
  
          SELECT @ErrorCode = @@ERROR,
                  @RowCount = @@ROWCOUNT
  
          IF( @ErrorCode <> 0 )
              GOTO Cleanup
  
          IF (@RowCount <> 0)
              SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
      END
  
      -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
      IF ((@TablesToDeleteFrom & 8) <> 0  AND
          (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
      BEGIN
          DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
  
          SELECT @ErrorCode = @@ERROR,
                  @RowCount = @@ROWCOUNT
  
          IF( @ErrorCode <> 0 )
              GOTO Cleanup
  
          IF (@RowCount <> 0)
              SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
      END
  
      -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
      IF ((@TablesToDeleteFrom & 1) <> 0 AND
          (@TablesToDeleteFrom & 2) <> 0 AND
          (@TablesToDeleteFrom & 4) <> 0 AND
          (@TablesToDeleteFrom & 8) <> 0 AND
          (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
      BEGIN
          DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
  
          SELECT @ErrorCode = @@ERROR,
                  @RowCount = @@ROWCOUNT
  
          IF( @ErrorCode <> 0 )
              GOTO Cleanup
  
          IF (@RowCount <> 0)
              SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
      END
  
      IF( @TranStarted = 1 )
      BEGIN
  	    SET @TranStarted = 0
  	    COMMIT TRANSACTION
      END
  
      RETURN 0
  
  Cleanup:
      SET @NumTablesDeletedFrom = 0
  
      IF( @TranStarted = 1 )
      BEGIN
          SET @TranStarted = 0
  	    ROLLBACK TRANSACTION
      END
  
      RETURN @ErrorCode
  
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  
  -- Restore the permissions
  EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Users_DeleteUser'
  GO
  
  /*************************************************************/
  /*************************************************************/
  --- aspnet_AnyDataInTables SP
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_AnyDataInTables')
                 AND (type = 'P')))
  DROP PROCEDURE [dbo].aspnet_AnyDataInTables
  GO
  CREATE PROCEDURE [dbo].aspnet_AnyDataInTables
      @TablesToCheck int
  AS
  BEGIN
      -- Check Membership table if (@TablesToCheck & 1) is set
      IF ((@TablesToCheck & 1) <> 0 AND
          (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
      BEGIN
          IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Membership))
          BEGIN
              SELECT N'aspnet_Membership'
              RETURN
          END
      END
  
      -- Check aspnet_Roles table if (@TablesToCheck & 2) is set
      IF ((@TablesToCheck & 2) <> 0  AND
          (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Roles') AND (type = 'V'))) )
      BEGIN
          IF (EXISTS(SELECT TOP 1 RoleId FROM dbo.aspnet_Roles))
          BEGIN
              SELECT N'aspnet_Roles'
              RETURN
          END
      END
  
      -- Check aspnet_Profile table if (@TablesToCheck & 4) is set
      IF ((@TablesToCheck & 4) <> 0  AND
          (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
      BEGIN
          IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Profile))
          BEGIN
              SELECT N'aspnet_Profile'
              RETURN
          END
      END
  
      -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 8) is set
      IF ((@TablesToCheck & 8) <> 0  AND
          (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
      BEGIN
          IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_PersonalizationPerUser))
          BEGIN
              SELECT N'aspnet_PersonalizationPerUser'
              RETURN
          END
      END
  
      -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 16) is set
      IF ((@TablesToCheck & 16) <> 0  AND
          (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_WebEvent_LogEvent') AND (type = 'P'))) )
      BEGIN
          IF (EXISTS(SELECT TOP 1 * FROM dbo.aspnet_WebEvent_Events))
          BEGIN
              SELECT N'aspnet_WebEvent_Events'
              RETURN
          END
      END
  
      -- Check aspnet_Users table if (@TablesToCheck & 1,2,4 & 8) are all set
      IF ((@TablesToCheck & 1) <> 0 AND
          (@TablesToCheck & 2) <> 0 AND
          (@TablesToCheck & 4) <> 0 AND
          (@TablesToCheck & 8) <> 0 AND
          (@TablesToCheck & 32) <> 0 AND
          (@TablesToCheck & 128) <> 0 AND
          (@TablesToCheck & 256) <> 0 AND
          (@TablesToCheck & 512) <> 0 AND
          (@TablesToCheck & 1024) <> 0)
      BEGIN
          IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Users))
          BEGIN
              SELECT N'aspnet_Users'
              RETURN
          END
          IF (EXISTS(SELECT TOP 1 ApplicationId FROM dbo.aspnet_Applications))
          BEGIN
              SELECT N'aspnet_Applications'
              RETURN
          END
      END
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  DECLARE @command nvarchar(400)
  SET @command = 'GRANT EXECUTE ON [dbo].aspnet_AnyDataInTables TO ' + QUOTENAME(user)
  EXEC (@command)
  GO
  
  -- Restore the permissions
  EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_AnyDataInTables'
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                  WHERE (name = N'vw_aspnet_Applications')
                    AND (type = 'V')))
  BEGIN
    PRINT 'Creating the vw_aspnet_Applications view...'
    EXEC('
    CREATE VIEW [dbo].[vw_aspnet_Applications]
    AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[LoweredApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]
    FROM [dbo].[aspnet_Applications]
    ')
  END
  
  -- Restore the permissions
  EXEC [dbo].aspnet_Setup_RestorePermissions N'vw_aspnet_Applications'
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                  WHERE (name = N'vw_aspnet_Users')
                    AND (type = 'V')))
  BEGIN
    PRINT 'Creating the vw_aspnet_Users view...'
    EXEC('
    CREATE VIEW [dbo].[vw_aspnet_Users]
    AS SELECT [dbo].[aspnet_Users].[ApplicationId], [dbo].[aspnet_Users].[UserId], [dbo].[aspnet_Users].[UserName], [dbo].[aspnet_Users].[LoweredUserName], [dbo].[aspnet_Users].[MobileAlias], [dbo].[aspnet_Users].[IsAnonymous], [dbo].[aspnet_Users].[LastActivityDate]
    FROM [dbo].[aspnet_Users]
    ')
  END
  
  -- Restore the permissions
  EXEC [dbo].aspnet_Setup_RestorePermissions N'vw_aspnet_Users'
  GO
  
  /*************************************************************/
  /*************************************************************/
  DECLARE @command nvarchar(4000)
  
  SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_Setup_RestorePermissions from ' + QUOTENAME(user)
  EXEC (@command)
  SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion from ' + QUOTENAME(user)
  EXEC (@command)
  GO
  
  DROP TABLE #aspnet_Permissions
  GO
  
  PRINT '----------------------------------------'
  PRINT 'Completed execution of InstallCommon.SQL'
  PRINT '----------------------------------------'
  /**********************************************************************/
  /* 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 sysobjects
                  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 sysobjects
                  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 sysobjects
               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 sysobjects
               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 sysobjects
               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 sysobjects
                  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 sysobjects
               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 sysobjects
               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 sysobjects
               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 sysobjects
               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.ApplicationId = a.ApplicationId 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
                  m.ApplicationId = a.ApplicationId AND
                  LOWER(@Email) = m.LoweredEmail
  
      IF (@@rowcount = 0)
          RETURN(1)
      RETURN(0)
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF ( EXISTS( SELECT name
               FROM sysobjects
               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 sysobjects
               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
      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 sysobjects
               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 sysobjects
               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 sysobjects
               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 sysobjects
               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 sysobjects
               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 sysobjects
               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 sysobjects
               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 sysobjects
               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(NOLOCK),
              dbo.aspnet_Applications a(NOLOCK),
              dbo.aspnet_Membership m(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 sysobjects
               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 sysobjects
               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 sysobjects
                  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 [dbo].[aspnet_Membership].[UserId],
              [dbo].[aspnet_Membership].[PasswordFormat],
              [dbo].[aspnet_Membership].[MobilePIN],
              [dbo].[aspnet_Membership].[Email],
              [dbo].[aspnet_Membership].[LoweredEmail],
              [dbo].[aspnet_Membership].[PasswordQuestion],
              [dbo].[aspnet_Membership].[PasswordAnswer],
              [dbo].[aspnet_Membership].[IsApproved],
              [dbo].[aspnet_Membership].[IsLockedOut],
              [dbo].[aspnet_Membership].[CreateDate],
              [dbo].[aspnet_Membership].[LastLoginDate],
              [dbo].[aspnet_Membership].[LastPasswordChangedDate],
              [dbo].[aspnet_Membership].[LastLockoutDate],
              [dbo].[aspnet_Membership].[FailedPasswordAttemptCount],
              [dbo].[aspnet_Membership].[FailedPasswordAttemptWindowStart],
              [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptCount],
              [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart],
              [dbo].[aspnet_Membership].[Comment],
              [dbo].[aspnet_Users].[ApplicationId],
              [dbo].[aspnet_Users].[UserName],
              [dbo].[aspnet_Users].[MobileAlias],
              [dbo].[aspnet_Users].[IsAnonymous],
              [dbo].[aspnet_Users].[LastActivityDate]
    FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Users]
        ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_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 sysusers
                    WHERE issqlrole = 1
                    AND name = N'aspnet_Membership_FullAccess'  ) )
  EXEC sp_addrole N'aspnet_Membership_FullAccess'
  
  IF ( NOT EXISTS ( SELECT name
                    FROM sysusers
                    WHERE issqlrole = 1
                    AND name = N'aspnet_Membership_BasicAccess'  ) )
  EXEC sp_addrole N'aspnet_Membership_BasicAccess'
  
  IF ( NOT EXISTS ( SELECT name
                    FROM sysusers
                    WHERE issqlrole = 1
                    AND name = N'aspnet_Membership_ReportingAccess'  ) )
  EXEC sp_addrole N'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 '--------------------------------------------'
  /**********************************************************************/
  /* InstallRoles.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 InstallRoles.SQL'
  PRINT '--------------------------------------'
  GO
  
  SET QUOTED_IDENTIFIER OFF -- We don't use quoted identifiers
  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 sysobjects
                  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 sysobjects
                  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 sysobjects
               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 sysobjects
               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 sysobjects
               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 sysobjects
                  WHERE (name = N'aspnet_Roles')
                    AND (type = 'U')))
  BEGIN
    PRINT 'Creating the aspnet_Roles table...'
    CREATE TABLE dbo.aspnet_Roles (
          ApplicationId    uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId),
          RoleId           uniqueidentifier    PRIMARY KEY  NONCLUSTERED DEFAULT NEWID(),
          RoleName         nvarchar(256)       NOT NULL,
          LoweredRoleName  nvarchar(256)       NOT NULL,
          Description      nvarchar(256)       )
   CREATE UNIQUE  CLUSTERED  INDEX aspnet_Roles_index1 ON  dbo.aspnet_Roles(ApplicationId, LoweredRoleName)
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                  WHERE (name = N'aspnet_UsersInRoles')
                    AND (type = 'U')))
  BEGIN
    PRINT 'Creating the aspnet_UsersInRoles table...'
    CREATE TABLE dbo.aspnet_UsersInRoles (
          UserId     uniqueidentifier NOT NULL PRIMARY KEY(UserId, RoleId) FOREIGN KEY REFERENCES dbo.aspnet_Users (UserId),
          RoleId     uniqueidentifier NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Roles (RoleId))
  
    CREATE INDEX aspnet_UsersInRoles_index ON  dbo.aspnet_UsersInRoles(RoleId)
  END
  
  
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_UsersInRoles_IsUserInRole')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_UsersInRoles_IsUserInRole
  GO
  
  CREATE PROCEDURE dbo.aspnet_UsersInRoles_IsUserInRole
      @ApplicationName  nvarchar(256),
      @UserName         nvarchar(256),
      @RoleName         nvarchar(256)
  AS
  BEGIN
      DECLARE @ApplicationId uniqueidentifier
      SELECT  @ApplicationId = NULL
      SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
      IF (@ApplicationId IS NULL)
          RETURN(2)
      DECLARE @UserId uniqueidentifier
      SELECT  @UserId = NULL
      DECLARE @RoleId uniqueidentifier
      SELECT  @RoleId = NULL
  
      SELECT  @UserId = UserId
      FROM    dbo.aspnet_Users
      WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
  
      IF (@UserId IS NULL)
          RETURN(2)
  
      SELECT  @RoleId = RoleId
      FROM    dbo.aspnet_Roles
      WHERE   LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
  
      IF (@RoleId IS NULL)
          RETURN(3)
  
      IF (EXISTS( SELECT * FROM dbo.aspnet_UsersInRoles WHERE  UserId = @UserId AND RoleId = @RoleId))
          RETURN(1)
      ELSE
          RETURN(0)
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_UsersInRoles_GetRolesForUser')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_UsersInRoles_GetRolesForUser
  GO
  
  CREATE PROCEDURE dbo.aspnet_UsersInRoles_GetRolesForUser
      @ApplicationName  nvarchar(256),
      @UserName         nvarchar(256)
  AS
  BEGIN
      DECLARE @ApplicationId uniqueidentifier
      SELECT  @ApplicationId = NULL
      SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
      IF (@ApplicationId IS NULL)
          RETURN(1)
      DECLARE @UserId uniqueidentifier
      SELECT  @UserId = NULL
  
      SELECT  @UserId = UserId
      FROM    dbo.aspnet_Users
      WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
  
      IF (@UserId IS NULL)
          RETURN(1)
  
      SELECT r.RoleName
      FROM   dbo.aspnet_Roles r, dbo.aspnet_UsersInRoles ur
      WHERE  r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId AND ur.UserId = @UserId
      ORDER BY r.RoleName
      RETURN (0)
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_Roles_CreateRole')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Roles_CreateRole
  GO
  CREATE PROCEDURE dbo.aspnet_Roles_CreateRole
      @ApplicationName  nvarchar(256),
      @RoleName         nvarchar(256)
  AS
  BEGIN
      DECLARE @ApplicationId uniqueidentifier
      SELECT  @ApplicationId = 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
  
      IF (EXISTS(SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId))
      BEGIN
          SET @ErrorCode = 1
          GOTO Cleanup
      END
  
      INSERT INTO dbo.aspnet_Roles
                  (ApplicationId, RoleName, LoweredRoleName)
           VALUES (@ApplicationId, @RoleName, LOWER(@RoleName))
  
      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 sysobjects
               WHERE (name = N'aspnet_Roles_DeleteRole')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Roles_DeleteRole
  GO
  
  CREATE PROCEDURE dbo.aspnet_Roles_DeleteRole
      @ApplicationName            nvarchar(256),
      @RoleName                   nvarchar(256),
      @DeleteOnlyIfRoleIsEmpty    bit
  AS
  BEGIN
      DECLARE @ApplicationId uniqueidentifier
      SELECT  @ApplicationId = NULL
      SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
      IF (@ApplicationId IS NULL)
          RETURN(1)
  
      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
  
      DECLARE @RoleId   uniqueidentifier
      SELECT  @RoleId = NULL
      SELECT  @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
  
      IF (@RoleId IS NULL)
      BEGIN
          SELECT @ErrorCode = 1
          GOTO Cleanup
      END
      IF (@DeleteOnlyIfRoleIsEmpty <> 0)
      BEGIN
          IF (EXISTS (SELECT RoleId FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId))
          BEGIN
              SELECT @ErrorCode = 2
              GOTO Cleanup
          END
      END
  
  
      DELETE FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId
  
      IF( @@ERROR <> 0 )
      BEGIN
          SET @ErrorCode = -1
          GOTO Cleanup
      END
  
      DELETE FROM dbo.aspnet_Roles WHERE @RoleId = RoleId  AND ApplicationId = @ApplicationId
  
      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 sysobjects
               WHERE (name = N'aspnet_Roles_RoleExists')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Roles_RoleExists
  GO
  
  CREATE PROCEDURE dbo.aspnet_Roles_RoleExists
      @ApplicationName  nvarchar(256),
      @RoleName         nvarchar(256)
  AS
  BEGIN
      DECLARE @ApplicationId uniqueidentifier
      SELECT  @ApplicationId = NULL
      SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
      IF (@ApplicationId IS NULL)
          RETURN(0)
      IF (EXISTS (SELECT RoleName FROM dbo.aspnet_Roles WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId ))
          RETURN(1)
      ELSE
          RETURN(0)
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_UsersInRoles_AddUsersToRoles')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
  GO
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_UsersInRoles_RemoveUsersFromRoles')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
  GO
  
  DECLARE @ver            int
  DECLARE @version        nchar(100)
  DECLARE @dot            int
  DECLARE @hyphen         int
  DECLARE @SqlToExec      nchar(4000)
  
  SELECT @ver = 7
  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 > 7)
  SELECT @SqlToExec = N'
  CREATE PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
  	@ApplicationName  nvarchar(256),
  	@UserNames		  nvarchar(4000),
  	@RoleNames		  nvarchar(4000),
  	@CurrentTimeUtc   datetime
  AS
  BEGIN
  	DECLARE @AppId uniqueidentifier
  	SELECT  @AppId = NULL
  	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
  	IF (@AppId IS NULL)
  		RETURN(2)
  	DECLARE @TranStarted   bit
  	SET @TranStarted = 0
  
  	IF( @@TRANCOUNT = 0 )
  	BEGIN
  		BEGIN TRANSACTION
  		SET @TranStarted = 1
  	END
  
  	DECLARE @tbNames	table(Name nvarchar(256) NOT NULL PRIMARY KEY)
  	DECLARE @tbRoles	table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
  	DECLARE @tbUsers	table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
  	DECLARE @Num		int
  	DECLARE @Pos		int
  	DECLARE @NextPos	int
  	DECLARE @Name		nvarchar(256)
  
  	SET @Num = 0
  	SET @Pos = 1
  	WHILE(@Pos <= LEN(@RoleNames))
  	BEGIN
  		SELECT @NextPos = CHARINDEX(N'','', @RoleNames,  @Pos)
  		IF (@NextPos = 0 OR @NextPos IS NULL)
  			SELECT @NextPos = LEN(@RoleNames) + 1
  		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
  		SELECT @Pos = @NextPos+1
  
  		INSERT INTO @tbNames VALUES (@Name)
  		SET @Num = @Num + 1
  	END
  
  	INSERT INTO @tbRoles
  	  SELECT RoleId
  	  FROM   dbo.aspnet_Roles ar, @tbNames t
  	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
  
  	IF (@@ROWCOUNT <> @Num)
  	BEGIN
  		SELECT TOP 1 Name
  		FROM   @tbNames
  		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
  		IF( @TranStarted = 1 )
  			ROLLBACK TRANSACTION
  		RETURN(2)
  	END
  
  	DELETE FROM @tbNames WHERE 1=1
  	SET @Num = 0
  	SET @Pos = 1
  
  	WHILE(@Pos <= LEN(@UserNames))
  	BEGIN
  		SELECT @NextPos = CHARINDEX(N'','', @UserNames,  @Pos)
  		IF (@NextPos = 0 OR @NextPos IS NULL)
  			SELECT @NextPos = LEN(@UserNames) + 1
  		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
  		SELECT @Pos = @NextPos+1
  
  		INSERT INTO @tbNames VALUES (@Name)
  		SET @Num = @Num + 1
  	END
  
  	INSERT INTO @tbUsers
  	  SELECT UserId
  	  FROM   dbo.aspnet_Users ar, @tbNames t
  	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
  
  	IF (@@ROWCOUNT <> @Num)
  	BEGIN
  		DELETE FROM @tbNames
  		WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE au.UserId = u.UserId)
  
  		INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
  		  SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
  		  FROM   @tbNames
  
  		INSERT INTO @tbUsers
  		  SELECT  UserId
  		  FROM	dbo.aspnet_Users au, @tbNames t
  		  WHERE   LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
  	END
  
  	IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
  	BEGIN
  		SELECT TOP 1 UserName, RoleName
  		FROM		 dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
  		WHERE		u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId
  
  		IF( @TranStarted = 1 )
  			ROLLBACK TRANSACTION
  		RETURN(3)
  	END
  
  	INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
  	SELECT UserId, RoleId
  	FROM @tbUsers, @tbRoles
  
  	IF( @TranStarted = 1 )
  		COMMIT TRANSACTION
  	RETURN(0)
  END'
  ELSE
  SELECT @SqlToExec = N'
  CREATE PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
  	@ApplicationName	nvarchar(256),
  	@UserNames			nvarchar(4000),
  	@RoleNames			nvarchar(4000),
  	@CurrentTimeUtc		datetime
  AS
  BEGIN
  	DECLARE @AppId uniqueidentifier
  	SELECT  @AppId = NULL
  	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
  	IF (@AppId IS NULL)
  		RETURN(2)
  
  	DECLARE @TranStarted   bit
  	SET @TranStarted = 0
  	IF( @@TRANCOUNT = 0 )
  	BEGIN
  		BEGIN TRANSACTION
  		SET @TranStarted = 1
  	END
  
  	DECLARE @RoleId		uniqueidentifier
  	DECLARE @UserId		uniqueidentifier
  	DECLARE @UserName	nvarchar(256)
  	DECLARE @RoleName	nvarchar(256)
  
  	DECLARE @CurrentPosU	int
  	DECLARE @NextPosU		int
  	DECLARE @CurrentPosR	int
  	DECLARE @NextPosR		int
  
  	SELECT  @CurrentPosU = 1
  
  	WHILE(@CurrentPosU <= LEN(@UserNames))
  	BEGIN
  		SELECT @NextPosU = CHARINDEX(N'','', @UserNames,  @CurrentPosU)
  		IF (@NextPosU = 0 OR @NextPosU IS NULL)
  			SELECT @NextPosU = LEN(@UserNames) + 1
  
  		SELECT @UserName = SUBSTRING(@UserNames, @CurrentPosU, @NextPosU - @CurrentPosU)
  		SELECT @CurrentPosU = @NextPosU+1
  
  		SELECT @CurrentPosR = 1
  		WHILE(@CurrentPosR <= LEN(@RoleNames))
  		BEGIN
  			SELECT @NextPosR = CHARINDEX(N'','', @RoleNames,  @CurrentPosR)
  			IF (@NextPosR = 0 OR @NextPosR IS NULL)
  				SELECT @NextPosR = LEN(@RoleNames) + 1
  			SELECT @RoleName = SUBSTRING(@RoleNames, @CurrentPosR, @NextPosR - @CurrentPosR)
  			SELECT @CurrentPosR = @NextPosR+1
  			SELECT @RoleId = NULL
  			SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @AppId
  			IF (@RoleId IS NULL)
  			BEGIN
  				SELECT @RoleName
  				IF( @TranStarted = 1 )
  					ROLLBACK TRANSACTION
  				RETURN(2)
  			END
  
  			SELECT @UserId = NULL
  			SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @AppId
  			IF (@UserId IS NULL)
  			BEGIN
  				EXEC dbo.aspnet_Users_CreateUser @AppId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
  			END
  
  			IF (EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId))
  			BEGIN
  				SELECT @UserName, @RoleName
  				IF( @TranStarted = 1 )
  					ROLLBACK TRANSACTION
  				RETURN(3)
  			END
  			INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId) VALUES(@UserId, @RoleId)
  		END
  	END
  	IF( @TranStarted = 1 )
  		COMMIT TRANSACTION
  	RETURN(0)
  END'
  
  EXEC sp_executesql @SqlToExec
  
  IF (@ver > 7)
  SELECT @SqlToExec = N'
  CREATE PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
  	@ApplicationName  nvarchar(256),
  	@UserNames		  nvarchar(4000),
  	@RoleNames		  nvarchar(4000)
  AS
  BEGIN
  	DECLARE @AppId uniqueidentifier
  	SELECT  @AppId = NULL
  	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
  	IF (@AppId IS NULL)
  		RETURN(2)
  
  
  	DECLARE @TranStarted   bit
  	SET @TranStarted = 0
  
  	IF( @@TRANCOUNT = 0 )
  	BEGIN
  		BEGIN TRANSACTION
  		SET @TranStarted = 1
  	END
  
  	DECLARE @tbNames  table(Name nvarchar(256) NOT NULL PRIMARY KEY)
  	DECLARE @tbRoles  table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
  	DECLARE @tbUsers  table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
  	DECLARE @Num	  int
  	DECLARE @Pos	  int
  	DECLARE @NextPos  int
  	DECLARE @Name	  nvarchar(256)
  	DECLARE @CountAll int
  	DECLARE @CountU	  int
  	DECLARE @CountR	  int
  
  
  	SET @Num = 0
  	SET @Pos = 1
  	WHILE(@Pos <= LEN(@RoleNames))
  	BEGIN
  		SELECT @NextPos = CHARINDEX(N'','', @RoleNames,  @Pos)
  		IF (@NextPos = 0 OR @NextPos IS NULL)
  			SELECT @NextPos = LEN(@RoleNames) + 1
  		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
  		SELECT @Pos = @NextPos+1
  
  		INSERT INTO @tbNames VALUES (@Name)
  		SET @Num = @Num + 1
  	END
  
  	INSERT INTO @tbRoles
  	  SELECT RoleId
  	  FROM   dbo.aspnet_Roles ar, @tbNames t
  	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
  	SELECT @CountR = @@ROWCOUNT
  
  	IF (@CountR <> @Num)
  	BEGIN
  		SELECT TOP 1 N'''', Name
  		FROM   @tbNames
  		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
  		IF( @TranStarted = 1 )
  			ROLLBACK TRANSACTION
  		RETURN(2)
  	END
  
  
  	DELETE FROM @tbNames WHERE 1=1
  	SET @Num = 0
  	SET @Pos = 1
  
  
  	WHILE(@Pos <= LEN(@UserNames))
  	BEGIN
  		SELECT @NextPos = CHARINDEX(N'','', @UserNames,  @Pos)
  		IF (@NextPos = 0 OR @NextPos IS NULL)
  			SELECT @NextPos = LEN(@UserNames) + 1
  		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
  		SELECT @Pos = @NextPos+1
  
  		INSERT INTO @tbNames VALUES (@Name)
  		SET @Num = @Num + 1
  	END
  
  	INSERT INTO @tbUsers
  	  SELECT UserId
  	  FROM   dbo.aspnet_Users ar, @tbNames t
  	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
  
  	SELECT @CountU = @@ROWCOUNT
  	IF (@CountU <> @Num)
  	BEGIN
  		SELECT TOP 1 Name, N''''
  		FROM   @tbNames
  		WHERE  LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE u.UserId = au.UserId)
  
  		IF( @TranStarted = 1 )
  			ROLLBACK TRANSACTION
  		RETURN(1)
  	END
  
  	SELECT  @CountAll = COUNT(*)
  	FROM	dbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
  	WHERE   ur.UserId = u.UserId AND ur.RoleId = r.RoleId
  
  	IF (@CountAll <> @CountU * @CountR)
  	BEGIN
  		SELECT TOP 1 UserName, RoleName
  		FROM		 @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
  		WHERE		 u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
  					 tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
  					 tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
  		IF( @TranStarted = 1 )
  			ROLLBACK TRANSACTION
  		RETURN(3)
  	END
  
  	DELETE FROM dbo.aspnet_UsersInRoles
  	WHERE UserId IN (SELECT UserId FROM @tbUsers)
  	  AND RoleId IN (SELECT RoleId FROM @tbRoles)
  	IF( @TranStarted = 1 )
  		COMMIT TRANSACTION
  	RETURN(0)
  END
  '
  ELSE
  SELECT @SqlToExec = N'
  CREATE PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
  	@ApplicationName  nvarchar(256),
  	@UserNames		  nvarchar(4000),
  	@RoleNames		  nvarchar(4000)
  AS
  BEGIN
  	DECLARE @AppId uniqueidentifier
  	SELECT  @AppId = NULL
  	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
  	IF (@AppId IS NULL)
  		RETURN(2)
  
  
  	DECLARE @TranStarted   bit
  	SET @TranStarted = 0
  
  	IF( @@TRANCOUNT = 0 )
  	BEGIN
  		BEGIN TRANSACTION
  		SET @TranStarted = 1
  	END
  
  	DECLARE @RoleId		uniqueidentifier
  	DECLARE @UserId		uniqueidentifier
  	DECLARE @UserName	nvarchar(256)
  	DECLARE @RoleName	nvarchar(256)
  
  	DECLARE @CurrentPosU	int
  	DECLARE @NextPosU		int
  	DECLARE @CurrentPosR	int
  	DECLARE @NextPosR		int
  
  	SELECT  @CurrentPosU = 1
  
  	WHILE(@CurrentPosU <= LEN(@UserNames))
  	BEGIN
  		SELECT @NextPosU = CHARINDEX(N'','', @UserNames,  @CurrentPosU)
  		IF (@NextPosU = 0  OR @NextPosU IS NULL)
  			SELECT @NextPosU = LEN(@UserNames)+1
  		SELECT @UserName = SUBSTRING(@UserNames, @CurrentPosU, @NextPosU - @CurrentPosU)
  		SELECT @CurrentPosU = @NextPosU+1
  
  		SELECT @CurrentPosR = 1
  		WHILE(@CurrentPosR <= LEN(@RoleNames))
  		BEGIN
  			SELECT @NextPosR = CHARINDEX(N'','', @RoleNames,  @CurrentPosR)
  			IF (@NextPosR = 0 OR @NextPosR IS NULL)
  				SELECT @NextPosR = LEN(@RoleNames)+1
  			SELECT @RoleName = SUBSTRING(@RoleNames, @CurrentPosR, @NextPosR - @CurrentPosR)
  			SELECT @CurrentPosR = @NextPosR+1
  
  			SELECT @RoleId = NULL
  			SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @AppId
  			IF (@RoleId IS NULL)
  			BEGIN
  				SELECT N'''', @RoleName
  				IF( @TranStarted = 1 )
  					ROLLBACK TRANSACTION
  				RETURN(2)
  			END
  
  			SELECT @UserId = NULL
  			SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @AppId
  			IF (@UserId IS NULL)
  			BEGIN
  				SELECT @UserName, N''''
  				IF( @TranStarted = 1 )
  					ROLLBACK TRANSACTION
  				RETURN(1)
  			END
  
  			IF (NOT(EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId)))
  			BEGIN
  				SELECT @UserName, @RoleName
  				IF( @TranStarted = 1 )
  					ROLLBACK TRANSACTION
  				RETURN(3)
  			END
  			DELETE FROM dbo.aspnet_UsersInRoles WHERE (UserId = @UserId AND RoleId = @RoleId)
  		END
  	END
  	IF( @TranStarted = 1 )
  		COMMIT TRANSACTION
  	RETURN(0)
  END
  '
  EXEC sp_executesql @SqlToExec
  GO
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_UsersInRoles_GetUsersInRoles')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_UsersInRoles_GetUsersInRoles
  GO
  
  CREATE PROCEDURE dbo.aspnet_UsersInRoles_GetUsersInRoles
      @ApplicationName  nvarchar(256),
      @RoleName         nvarchar(256)
  AS
  BEGIN
      DECLARE @ApplicationId uniqueidentifier
      SELECT  @ApplicationId = NULL
      SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
      IF (@ApplicationId IS NULL)
          RETURN(1)
       DECLARE @RoleId uniqueidentifier
       SELECT  @RoleId = NULL
  
       SELECT  @RoleId = RoleId
       FROM    dbo.aspnet_Roles
       WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
  
       IF (@RoleId IS NULL)
           RETURN(1)
  
      SELECT u.UserName
      FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
      WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
      ORDER BY u.UserName
      RETURN(0)
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_UsersInRoles_FindUsersInRole')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_UsersInRoles_FindUsersInRole
  GO
  
  CREATE PROCEDURE dbo.aspnet_UsersInRoles_FindUsersInRole
      @ApplicationName  nvarchar(256),
      @RoleName         nvarchar(256),
      @UserNameToMatch  nvarchar(256)
  AS
  BEGIN
      DECLARE @ApplicationId uniqueidentifier
      SELECT  @ApplicationId = NULL
      SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
      IF (@ApplicationId IS NULL)
          RETURN(1)
       DECLARE @RoleId uniqueidentifier
       SELECT  @RoleId = NULL
  
       SELECT  @RoleId = RoleId
       FROM    dbo.aspnet_Roles
       WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
  
       IF (@RoleId IS NULL)
           RETURN(1)
  
      SELECT u.UserName
      FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
      WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId AND LoweredUserName LIKE LOWER(@UserNameToMatch)
      ORDER BY u.UserName
      RETURN(0)
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (EXISTS (SELECT name
                FROM sysobjects
               WHERE (name = N'aspnet_Roles_GetAllRoles')
                 AND (type = 'P')))
  DROP PROCEDURE dbo.aspnet_Roles_GetAllRoles
  GO
  
  CREATE PROCEDURE dbo.aspnet_Roles_GetAllRoles (
      @ApplicationName           nvarchar(256))
  AS
  BEGIN
      DECLARE @ApplicationId uniqueidentifier
      SELECT  @ApplicationId = NULL
      SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
      IF (@ApplicationId IS NULL)
          RETURN
      SELECT RoleName
      FROM   dbo.aspnet_Roles WHERE ApplicationId = @ApplicationId
      ORDER BY RoleName
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                  WHERE (name = N'vw_aspnet_Roles')
                    AND (type = 'V')))
  BEGIN
    PRINT 'Creating the vw_aspnet_Roles view...'
    EXEC(N'
    CREATE VIEW [dbo].[vw_aspnet_Roles]
    AS SELECT [dbo].[aspnet_Roles].[ApplicationId], [dbo].[aspnet_Roles].[RoleId], [dbo].[aspnet_Roles].[RoleName], [dbo].[aspnet_Roles].[LoweredRoleName], [dbo].[aspnet_Roles].[Description]
    FROM [dbo].[aspnet_Roles]
    ')
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                  WHERE (name = N'vw_aspnet_UsersInRoles')
                    AND (type = 'V')))
  BEGIN
    PRINT 'Creating the vw_aspnet_UsersInRoles view...'
    EXEC(N'
    CREATE VIEW [dbo].[vw_aspnet_UsersInRoles]
    AS SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]
    FROM [dbo].[aspnet_UsersInRoles]
    ')
  END
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  --
  --Create Role Manager 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'Role Manager', N'1', 1, 1
  GO
  
  /*************************************************************/
  /*************************************************************/
  
  --
  --Create Role Manager roles
  --
  
  IF ( NOT EXISTS ( SELECT name
                    FROM sysusers
                    WHERE issqlrole = 1
                    AND name = N'aspnet_Roles_FullAccess'  ) )
  EXEC sp_addrole N'aspnet_Roles_FullAccess'
  
  IF ( NOT EXISTS ( SELECT name
                    FROM sysusers
                    WHERE issqlrole = 1
                    AND name = N'aspnet_Roles_BasicAccess'  ) )
  EXEC sp_addrole N'aspnet_Roles_BasicAccess'
  
  IF ( NOT EXISTS ( SELECT name
                    FROM sysusers
                    WHERE issqlrole = 1
                    AND name = N'aspnet_Roles_ReportingAccess'  ) )
  EXEC sp_addrole N'aspnet_Roles_ReportingAccess'
  GO
  
  EXEC sp_addrolemember N'aspnet_Roles_BasicAccess', N'aspnet_Roles_FullAccess'
  EXEC sp_addrolemember N'aspnet_Roles_ReportingAccess', N'aspnet_Roles_FullAccess'
  GO
  
  --
  --Stored Procedure rights for BasicAccess
  --
  GRANT EXECUTE ON dbo.aspnet_UsersInRoles_IsUserInRole TO aspnet_Roles_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_UsersInRoles_GetRolesForUser TO aspnet_Roles_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Roles_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Roles_BasicAccess
  GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Roles_BasicAccess
  
  --
  --Stored Procedure rights for ReportingAccess
  --
  GRANT EXECUTE ON dbo.aspnet_UsersInRoles_IsUserInRole TO aspnet_Roles_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_UsersInRoles_GetRolesForUser TO aspnet_Roles_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_Roles_RoleExists TO aspnet_Roles_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_UsersInRoles_GetUsersInRoles TO aspnet_Roles_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_UsersInRoles_FindUsersInRole TO aspnet_Roles_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_Roles_GetAllRoles TO aspnet_Roles_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Roles_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Roles_ReportingAccess
  GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Roles_ReportingAccess
  
  --
  --Additional stored procedure rights for FullAccess
  --
  
  GRANT EXECUTE ON dbo.aspnet_Roles_CreateRole TO aspnet_Roles_FullAccess
  GRANT EXECUTE ON dbo.aspnet_Roles_DeleteRole TO aspnet_Roles_FullAccess
  GRANT EXECUTE ON dbo.aspnet_UsersInRoles_AddUsersToRoles TO aspnet_Roles_FullAccess
  GRANT EXECUTE ON dbo.aspnet_UsersInRoles_RemoveUsersFromRoles TO aspnet_Roles_FullAccess
  
  --
  --View rights
  --
  GRANT SELECT ON dbo.vw_aspnet_Applications TO aspnet_Roles_ReportingAccess
  GRANT SELECT ON dbo.vw_aspnet_Users TO aspnet_Roles_ReportingAccess
  
  GRANT SELECT ON dbo.vw_aspnet_Roles TO aspnet_Roles_ReportingAccess
  GRANT SELECT ON dbo.vw_aspnet_UsersInRoles TO aspnet_Roles_ReportingAccess
  
  GO
  
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  /*************************************************************/
  
  DECLARE @command nvarchar(4000)
  SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion FROM ' + QUOTENAME(user)
  EXECUTE (@command)
  GO
  
  PRINT '---------------------------------------'
  PRINT 'Completed execution of InstallRoles.SQL'
  PRINT '---------------------------------------'
  
  