/*   Please Read  

For interim builds of CS, you may have an incorrect PK on aspnet_Membership. You should first try to delete:

Alter table aspnet_Membership Drop Constraint PK_aspnet_Membership

*/

if exists (select * from dbo.sysindexes where name = N'IX_aspnet_Membership' and id = object_id(N'[dbo].[aspnet_Membership]'))
drop index [dbo].[aspnet_Membership].[IX_aspnet_Membership]
Go
if exists (select * from dbo.sysindexes where name = N'aspnet_Membership_index' and id = object_id(N'[dbo].[aspnet_Membership]'))
drop index [dbo].[aspnet_Membership].[aspnet_Membership_index]
GO



Print 'Creating New User Tabel'
Create TABLE [dbo].[cs_Users] (
	[MembershipID] UNIQUEIDENTIFIER  NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Users(UserId) ,
	[UserID] [int] IDENTITY (2100, 1) NOT NULL PRIMARY KEY CLUSTERED ,
	[ForceLogin] [bit] NOT NULL CONSTRAINT [DF_cs_User_ForceLogin] DEFAULT (0),
	[UserAccountStatus] [smallint] NOT NULL CONSTRAINT [DF_cs_User_Approved] DEFAULT (1),
	[AppUserToken] [varchar] (128) NULL ,
	[LastActivity] [datetime] NOT NULL CONSTRAINT [DF_cs_User_LastActivity] DEFAULT (getdate()),
	[LastAction] [nvarchar] (1024)  NULL CONSTRAINT [DF_cs_User_LastAction] DEFAULT (''),
	
)
Go        
Print 'Updated New User Table'
Set Identity_Insert cs_Users On
Insert cs_Users (MembershipID, UserID, ForceLogin, UserAccountStatus, AppUserToken, LastActivity, LastAction)
Select UserID, cs_UserID, cs_ForceLogin, cs_UserAccountStatus, cs_AppUserToken, cs_LastActivity, cs_LastAction FROM aspnet_Membership
Set Identity_Insert cs_Users OFF
Go

Alter table [dbo].[aspnet_Membership] Add [ApplicationID] UNIQUEIDENTIFIER
Go
Update aspnet_Membership 
Set [ApplicationID] = aspnet_Users.ApplicationID
From aspnet_Users
where aspnet_Membership.UserID = aspnet_Users.UserID
Go
Alter table [dbo].[aspnet_Membership] Alter Column [ApplicationID] UNIQUEIDENTIFIER NOT NULL
Go
ALTER TABLE [dbo].[aspnet_Membership] ADD Constraint [FK_aspnet_Applications] FOREIGN KEY
	(
		[ApplicationId]
	) REFERENCES [aspnet_Applications] (
		[ApplicationId]
	)
GO

ALTER TABLE cs_UserProfile ADD Constraint [FK_cs_Users] FOREIGN KEY  ([UserID]) REFERENCES [cs_Users] ([UserID])


GO



--If this fails, it is OK
Print 'If this single part fails it is OK'

if exists (select * from dbo.sysindexes where name = N'aspnet_Membership_index' and id = object_id(N'[dbo].[aspnet_Membership_index]'))
drop index [dbo].[aspnet_Membership].[aspnet_Membership_index]
Go

Go

 CREATE  CLUSTERED  INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership]([ApplicationId], [LoweredEmail]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
Print 'End Fail Safe'

Print 'Removing obsolete fields from the membership table'
-- drop any defaults from the CS columns on the aspnet_membership table
IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = 'D' and d.name = 'DF_aspnet_membership_Approved')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_Approved
END

IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = 'D' and d.name = 'DF_aspnet_membership_ForceLogin')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_ForceLogin
END

IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = 'D' and d.name = 'DF_aspnet_membership_ForcLogin')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_ForcLogin
END

IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = 'D' and d.name = 'DF_aspnet_membership_LastAction')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_LastAction
END

IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = 'D' and d.name = 'DF_aspnet_membership_LastActivity')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_LastActivity
END

IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = 'D' and d.name = 'DF_aspnet_Membership_PasswordFormat')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_Membership_PasswordFormat
END

-- drop the special columns in aspnet_Membership
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'cs_UserID' and TABLE_NAME = 'aspnet_Membership' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_UserID]
END

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'cs_ForceLogin' and TABLE_NAME = 'aspnet_Membership' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_ForceLogin]
END

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'cs_UserAccountStatus' and TABLE_NAME = 'aspnet_Membership' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_UserAccountStatus]
END

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'cs_AppUserToken' and TABLE_NAME = 'aspnet_Membership' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_AppUserToken]
END

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'cs_LastActivity' and TABLE_NAME = 'aspnet_Membership' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_LastActivity]
END

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'cs_LastAction' and TABLE_NAME = 'aspnet_Membership' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_LastAction]
END

IF EXISTS(SELECT * FROM sysobjects where type = 'FN' and name = 'GetAnonymousUserID')
BEGIN
	DROP FUNCTION GetAnonymousUserID
end

IF EXISTS(SELECT * FROM sysobjects where type = 'FN' and name = 'HasReadPost')
begin
	DROP FUNCTION HasReadPost
end


ALTER TABLE [dbo].[aspnet_Profile] ALTER COLUMN [LastUpdatedDate] [datetime] NOT NULL

Go

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO


PRINT N'Dropping index [aspnet_UsersInRoles_index2] from [dbo].[aspnet_UsersInRoles]'
GO
DROP INDEX [dbo].[aspnet_UsersInRoles].[aspnet_UsersInRoles_index2]


GO
PRINT N'Dropping index [aspnet_Membership_index] from [dbo].[aspnet_Membership]'
GO
--DROP INDEX [dbo].[aspnet_Membership].[aspnet_Membership_index]
GO


PRINT N'Dropping index [aspnet_Roles_index1] from [dbo].[aspnet_Roles]'
GO
DROP INDEX [dbo].[aspnet_Roles].[aspnet_Roles_index1]


GO
PRINT N'Dropping index [aspnet_Users_Index] from [dbo].[aspnet_Users]'
GO
DROP INDEX [dbo].[aspnet_Users].[aspnet_Users_Index]


GO
PRINT N'Dropping index [aspnet_UsersInRoles_index1] from [dbo].[aspnet_UsersInRoles]'
GO
DROP INDEX [dbo].[aspnet_UsersInRoles].[aspnet_UsersInRoles_index1]


GO
PRINT N'Dropping [dbo].[vw_aspnet_SchemaVersions]'
GO
DROP VIEW [dbo].[vw_aspnet_SchemaVersions]


GO
PRINT N'Altering [dbo].[aspnet_Profile]'
GO
ALTER TABLE [dbo].[aspnet_Profile] ALTER COLUMN [LastUpdatedDate] [datetime] NOT NULL



GO

PRINT N'Creating [dbo].[aspnet_GetUtcDate]'
GO

CREATE PROCEDURE [dbo].aspnet_GetUtcDate
    @TimeZoneAdjustment INT,
    @DateNow            DATETIME OUTPUT
AS
BEGIN
    SELECT @DateNow = GETUTCDATE()
END
                                                                                                                                                                                                                                         


GO
PRINT N'Altering [dbo].[aspnet_RegisterSchemaVersion]'
GO

ALTER 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
PRINT N'Altering [dbo].[vw_aspnet_Profiles]'
GO

  ALTER VIEW [dbo].[vw_aspnet_Profiles]
  AS SELECT [dbo].[aspnet_Profile].[UserId], [dbo].[aspnet_Profile].[LastUpdatedDate],
      [DataSize]=  DATALENGTH([dbo].[aspnet_Profile].[PropertyNames])
                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesString])
                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesBinary])
  FROM [dbo].[aspnet_Profile]
  


GO
PRINT N'Altering [dbo].[aspnet_CheckSchemaVersion]'
GO

ALTER 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
PRINT N'Creating [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
PRINT N'Altering [dbo].[vw_aspnet_UsersInRoles]'
GO

  ALTER VIEW [dbo].[vw_aspnet_UsersInRoles]
  AS SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]
  FROM [dbo].[aspnet_UsersInRoles]
  


GO
PRINT N'Altering [dbo].[aspnet_Applications_CreateApplication]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_Roles]'
GO
ALTER TABLE [dbo].[aspnet_Roles] ALTER COLUMN [RoleId] [uniqueidentifier] NOT NULL



GO
PRINT N'Creating index [aspnet_Roles_index1] on [dbo].[aspnet_Roles]'
GO
CREATE UNIQUE CLUSTERED INDEX [aspnet_Roles_index1] ON [dbo].[aspnet_Roles] ([ApplicationId], [LoweredRoleName])


GO
PRINT N'Altering [dbo].[aspnet_Membership_GetUserByUserId]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_GetUserByUserId
    @UserId               UNIQUEIDENTIFIER,
    @TimeZoneAdjustment   INT,
    @UpdateLastActivity   BIT = 0
AS
BEGIN
    IF ( @UpdateLastActivity = 1 )
    BEGIN
        DECLARE @DateTimeNowUTC DATETIME
        EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @DateTimeNowUTC
        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
PRINT N'Altering [dbo].[aspnet_Membership_UpdateLastLoginAndActivityDates]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_UpdateLastLoginAndActivityDates
    @ApplicationName          NVARCHAR(256),
    @UserName                 NVARCHAR(256),
    @TimeZoneAdjustment       INT
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
    END

    DECLARE @TranStarted   BIT
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
	  SET @TranStarted = 0

    DECLARE @DateTimeNowUTC DATETIME
    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT

    UPDATE  dbo.aspnet_Membership
    SET     LastLoginDate = @DateTimeNowUTC
    WHERE   UserId = @UserId

    IF( @@ERROR <> 0 )
        GOTO Cleanup

    UPDATE  dbo.aspnet_Users
    SET     LastActivityDate = @DateTimeNowUTC
    WHERE   @UserId = UserId

    IF( @@ERROR <> 0 )
        GOTO Cleanup

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    RETURN

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
	    ROLLBACK TRANSACTION
    END

    RETURN -1

END



GO
PRINT N'Altering [dbo].[aspnet_Profile_GetProfiles]'
GO

ALTER PROCEDURE dbo.aspnet_Profile_GetProfiles
    @ApplicationName        NVARCHAR(256),
    @ProfileAuthOptions     INT,
    @PageIndex              INT,
    @PageSize               INT,
    @TimeZoneAdjustment     INT,
    @UserNameToMatch        NVARCHAR(256) = NULL,
    @InactiveSinceDate      DATETIME      = NULL
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN

    IF ((NOT(@InactiveSinceDate IS NULL)) AND (@InactiveSinceDate > CONVERT(DATETIME, '17540101', 112)) AND  (@InactiveSinceDate < CONVERT(DATETIME, '99980101', 112)))
        SELECT @InactiveSinceDate = DATEADD(n, -@TimeZoneAdjustment, @InactiveSinceDate)

    -- 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_Profile p
        WHERE   ApplicationId = @ApplicationId
            AND u.UserId = p.UserId
            AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate)
            AND (     (@ProfileAuthOptions = 2)
                   OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                   OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
                 )
            AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))
        ORDER BY UserName

    SELECT  u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
            DATALENGTH(p.PropertyNames) + DATALENGTH(p.PropertyValuesString) + DATALENGTH(p.PropertyValuesBinary)
    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p, #PageIndexForUsers i
    WHERE   u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound

    SELECT COUNT(*)
    FROM   #PageIndexForUsers

    DROP TABLE #PageIndexForUsers
END




GO
PRINT N'Altering [dbo].[aspnet_Membership_FindUsersByName]'
GO
ALTER 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
PRINT N'Altering [dbo].[aspnet_Membership_GetAllUsers]'
GO
ALTER 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
PRINT N'Altering [dbo].[vw_aspnet_MembershipUsers]'
GO

  ALTER 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]
  


GO
PRINT N'Altering [dbo].[aspnet_Membership_GetPassword]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_GetPassword
    @ApplicationName                NVARCHAR(256),
    @UserName                       NVARCHAR(256),
    @MaxInvalidPasswordAttempts     INT,
    @PasswordAttemptWindow          INT,
    @TimeZoneAdjustment             INT,
    @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

    DECLARE @DateTimeNowUTC DATETIME
    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT

    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( @DateTimeNowUTC > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC
                SET @FailedPasswordAnswerAttemptCount = 1
            END
            ELSE
            BEGIN
                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
                SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC
            END

            BEGIN
                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                BEGIN
                    SET @IsLockedOut = 1
                    SET @LastLockoutDate = @DateTimeNowUTC
                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
PRINT N'Altering [dbo].[aspnet_UsersInRoles_FindUsersInRole]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]'
GO

ALTER PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
    @ApplicationName  NVARCHAR(256),
    @UserNames        NVARCHAR(4000),
    @RoleNames        NVARCHAR(4000)
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 @TranStarted   BIT
    DECLARE @ErrorCode INT
    SET @ErrorCode   = 0
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0


    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 = @ApplicationId
            IF (@RoleId IS NULL)
            BEGIN
                SELECT N'', @RoleName
                SET @ErrorCode = 2
                GOTO Cleanup
            END

            SELECT @UserId = NULL
            SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
            IF (@UserId IS NULL)
            BEGIN
                SELECT @UserName, N''
                SET @ErrorCode = 1
                GOTO Cleanup
            END

            IF (NOT(EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId)))
            BEGIN
                SELECT @UserName, @RoleName
                SET @ErrorCode = 3
                GOTO Cleanup
            END
            DELETE FROM dbo.aspnet_UsersInRoles WHERE (UserId = @UserId AND RoleId = @RoleId)
        END
    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
PRINT N'Altering [dbo].[aspnet_Membership_GetUserByEmail]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_GetUserByEmail
    @ApplicationName  NVARCHAR(256),
    @Email            NVARCHAR(256)
AS
BEGIN
    IF( @Email IS NULL )
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                m.LoweredEmail IS NULL
    ELSE
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                LOWER(@Email) = m.LoweredEmail

    IF (@@rowcount = 0)
        RETURN(1)
    RETURN(0)
END



GO
PRINT N'Altering [dbo].[aspnet_Profile_GetProperties]'
GO

ALTER PROCEDURE dbo.aspnet_Profile_GetProperties
    @ApplicationName      NVARCHAR(256),
    @UserName             NVARCHAR(256),
    @TimeZoneAdjustment   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

    DECLARE @UserId UNIQUEIDENTIFIER
    SELECT  @UserId = NULL
    DECLARE @DateTimeNowUTC DATETIME
    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT

    SELECT @UserId = UserId
    FROM   dbo.aspnet_Users
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)

    IF (@UserId IS NULL)
        RETURN
    SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
    FROM         dbo.aspnet_Profile
    WHERE        UserId = @UserId

    IF (@@ROWCOUNT > 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate=@DateTimeNowUTC
        WHERE  UserId = @UserId
    END
END



GO
PRINT N'Altering [dbo].[aspnet_Roles_DeleteRole]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_UsersInRoles_GetRolesForUser]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_Membership_GetPasswordWithFormat]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
    @ApplicationName                NVARCHAR(256),
    @UserName                       NVARCHAR(256)
AS
BEGIN
    DECLARE @Password                               NVARCHAR(128)
    DECLARE @PasswordFormat                         INT
    DECLARE @PasswordSalt                           NVARCHAR(128)
    DECLARE @IsLockedOut                            BIT
    DECLARE @FailedPasswordAttemptCount             INT
    DECLARE @FailedPasswordAnswerAttemptCount       INT
    DECLARE @IsApproved                             BIT

    SELECT  @Password = m.Password,
            @PasswordFormat = m.PasswordFormat,
            @PasswordSalt = m.PasswordSalt,
            @IsLockedOut = m.IsLockedOut,
            @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
            @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
            @IsApproved = m.IsApproved
    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( @@rowcount = 0 )
        RETURN 1

    IF( @IsLockedOut = 1 )
        RETURN 99

    SELECT @Password,
           @PasswordFormat,
           @PasswordSalt,
           @FailedPasswordAttemptCount,
           @FailedPasswordAnswerAttemptCount,
           @IsApproved

    RETURN 0
END



GO
PRINT N'Altering [dbo].[aspnet_UsersInRoles_IsUserInRole]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_UsersInRoles_GetUsersInRoles]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_Membership_GetNumberOfUsersOnline]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
    @ApplicationName            NVARCHAR(256),
    @MinutesSinceLastInActive   INT,
    @TimeZoneAdjustment         INT
AS
BEGIN
    DECLARE @DateActive DATETIME
    SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive + @TimeZoneAdjustment), GETDATE())

    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
PRINT N'Altering [dbo].[aspnet_Users_CreateUser]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_Membership_UpdateUserInfo]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
    @ApplicationName                NVARCHAR(256),
    @UserName                       NVARCHAR(256),
    @IsPasswordCorrect              BIT,
    @UpdateLastLoginActivityDate    BIT,
    @MaxInvalidPasswordAttempts     INT,
    @PasswordAttemptWindow          INT,
    @TimeZoneAdjustment             INT
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

    DECLARE @DateTimeNowUTC DATETIME
    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
    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( @DateTimeNowUTC > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
        BEGIN
            SET @FailedPasswordAttemptWindowStart = @DateTimeNowUTC
            SET @FailedPasswordAttemptCount = 1
        END
        ELSE
        BEGIN
            SET @FailedPasswordAttemptWindowStart = @DateTimeNowUTC
            SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
        END

        BEGIN
            IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
            BEGIN
                SET @IsLockedOut = 1
                SET @LastLockoutDate = @DateTimeNowUTC
            END
        END
    END
    ELSE
    BEGIN
        IF( @UpdateLastLoginActivityDate = 1 )
        BEGIN
            UPDATE  dbo.aspnet_Membership
            SET     LastLoginDate = @DateTimeNowUTC
            WHERE   UserId = @UserId

            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END

            UPDATE  dbo.aspnet_Users
            SET     LastActivityDate = @DateTimeNowUTC
            WHERE   @UserId = UserId

            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END
        END

        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

    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
PRINT N'Altering [dbo].[vw_aspnet_Roles]'
GO

  ALTER 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]
  


GO
PRINT N'Altering [dbo].[aspnet_Users_DeleteUser]'
GO
ALTER 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'aspnet_Membership') AND (type = 'U'))))
    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'aspnet_UsersInRoles') AND (type = 'U'))) )
    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'aspnet_Profile') AND (type = 'U'))) )
    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'aspnet_PersonalizationPerUser') AND (type = 'U'))) )
    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
PRINT N'Altering [dbo].[vw_aspnet_Applications]'
GO

  ALTER VIEW [dbo].[vw_aspnet_Applications]
  AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]
  FROM [dbo].[aspnet_Applications]
  


GO
PRINT N'Altering [dbo].[aspnet_Membership_ResetPassword]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_ResetPassword
    @ApplicationName             NVARCHAR(256),
    @UserName                    NVARCHAR(256),
    @NewPassword                 NVARCHAR(128),
    @MaxInvalidPasswordAttempts  INT,
    @PasswordAttemptWindow       INT,
    @PasswordSalt                NVARCHAR(128),
    @TimeZoneAdjustment          INT,
    @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
            LoweredApplicationName = 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
    DECLARE @DateTimeNowUTC DATETIME
    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT

    UPDATE dbo.aspnet_Membership
    SET    Password = @NewPassword,
           LastPasswordChangedDate = @DateTimeNowUTC,
           PasswordFormat = @PasswordFormat,
           PasswordSalt = @PasswordSalt
    WHERE  @UserId = UserId AND
           ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )

    IF ( @@ROWCOUNT = 0 )
        BEGIN
            IF( @DateTimeNowUTC > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC
                SET @FailedPasswordAnswerAttemptCount = 1
            END
            ELSE
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC
                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
            END
            
            BEGIN
                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                BEGIN
                    SET @IsLockedOut = 1
                    SET @LastLockoutDate = @DateTimeNowUTC
                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
PRINT N'Altering [dbo].[aspnet_Profile_GetNumberOfInactiveProfiles]'
GO

ALTER PROCEDURE dbo.aspnet_Profile_GetNumberOfInactiveProfiles
    @ApplicationName        NVARCHAR(256),
    @ProfileAuthOptions     INT,
    @InactiveSinceDate      DATETIME,
    @TimeZoneAdjustment     INT
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
    BEGIN
        SELECT 0
        RETURN
    END

    IF (@InactiveSinceDate > CONVERT(DATETIME, '17540101', 112) AND  @InactiveSinceDate < CONVERT(DATETIME,'99980101', 112))
        SELECT @InactiveSinceDate = DATEADD(n, -@TimeZoneAdjustment, @InactiveSinceDate)

    SELECT  COUNT(*)
    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
    WHERE   ApplicationId = @ApplicationId
        AND u.UserId = p.UserId
        AND (LastActivityDate <= @InactiveSinceDate)
        AND (
                (@ProfileAuthOptions = 2)
                OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
            )
END

GO
PRINT N'Altering [dbo].[aspnet_Membership_FindUsersByName]'
GO
ALTER 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
PRINT N'Altering [dbo].[aspnet_Membership_GetAllUsers]'
GO
ALTER 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
PRINT N'Altering [dbo].[vw_aspnet_MembershipUsers]'
GO

  ALTER 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]
  


GO
PRINT N'Altering [dbo].[aspnet_Membership_GetPassword]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_GetPassword
    @ApplicationName                NVARCHAR(256),
    @UserName                       NVARCHAR(256),
    @MaxInvalidPasswordAttempts     INT,
    @PasswordAttemptWindow          INT,
    @TimeZoneAdjustment             INT,
    @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

    DECLARE @DateTimeNowUTC DATETIME
    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT

    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( @DateTimeNowUTC > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC
                SET @FailedPasswordAnswerAttemptCount = 1
            END
            ELSE
            BEGIN
                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
                SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC
            END

            BEGIN
                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                BEGIN
                    SET @IsLockedOut = 1
                    SET @LastLockoutDate = @DateTimeNowUTC
                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
PRINT N'Altering [dbo].[aspnet_UsersInRoles_FindUsersInRole]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]'
GO

ALTER PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
    @ApplicationName  NVARCHAR(256),
    @UserNames        NVARCHAR(4000),
    @RoleNames        NVARCHAR(4000)
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 @TranStarted   BIT
    DECLARE @ErrorCode INT
    SET @ErrorCode   = 0
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0


    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 = @ApplicationId
            IF (@RoleId IS NULL)
            BEGIN
                SELECT N'', @RoleName
                SET @ErrorCode = 2
                GOTO Cleanup
            END

            SELECT @UserId = NULL
            SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
            IF (@UserId IS NULL)
            BEGIN
                SELECT @UserName, N''
                SET @ErrorCode = 1
                GOTO Cleanup
            END

            IF (NOT(EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId)))
            BEGIN
                SELECT @UserName, @RoleName
                SET @ErrorCode = 3
                GOTO Cleanup
            END
            DELETE FROM dbo.aspnet_UsersInRoles WHERE (UserId = @UserId AND RoleId = @RoleId)
        END
    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
PRINT N'Altering [dbo].[aspnet_Membership_GetUserByEmail]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_GetUserByEmail
    @ApplicationName  NVARCHAR(256),
    @Email            NVARCHAR(256)
AS
BEGIN
    IF( @Email IS NULL )
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                m.LoweredEmail IS NULL
    ELSE
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                LOWER(@Email) = m.LoweredEmail

    IF (@@rowcount = 0)
        RETURN(1)
    RETURN(0)
END



GO
PRINT N'Altering [dbo].[aspnet_Profile_GetProperties]'
GO

ALTER PROCEDURE dbo.aspnet_Profile_GetProperties
    @ApplicationName      NVARCHAR(256),
    @UserName             NVARCHAR(256),
    @TimeZoneAdjustment   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

    DECLARE @UserId UNIQUEIDENTIFIER
    SELECT  @UserId = NULL
    DECLARE @DateTimeNowUTC DATETIME
    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT

    SELECT @UserId = UserId
    FROM   dbo.aspnet_Users
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)

    IF (@UserId IS NULL)
        RETURN
    SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
    FROM         dbo.aspnet_Profile
    WHERE        UserId = @UserId

    IF (@@ROWCOUNT > 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate=@DateTimeNowUTC
        WHERE  UserId = @UserId
    END
END



GO
PRINT N'Altering [dbo].[aspnet_Roles_DeleteRole]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_UsersInRoles_GetRolesForUser]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_Membership_GetPasswordWithFormat]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
    @ApplicationName                NVARCHAR(256),
    @UserName                       NVARCHAR(256)
AS
BEGIN
    DECLARE @Password                               NVARCHAR(128)
    DECLARE @PasswordFormat                         INT
    DECLARE @PasswordSalt                           NVARCHAR(128)
    DECLARE @IsLockedOut                            BIT
    DECLARE @FailedPasswordAttemptCount             INT
    DECLARE @FailedPasswordAnswerAttemptCount       INT
    DECLARE @IsApproved                             BIT

    SELECT  @Password = m.Password,
            @PasswordFormat = m.PasswordFormat,
            @PasswordSalt = m.PasswordSalt,
            @IsLockedOut = m.IsLockedOut,
            @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
            @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
            @IsApproved = m.IsApproved
    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( @@rowcount = 0 )
        RETURN 1

    IF( @IsLockedOut = 1 )
        RETURN 99

    SELECT @Password,
           @PasswordFormat,
           @PasswordSalt,
           @FailedPasswordAttemptCount,
           @FailedPasswordAnswerAttemptCount,
           @IsApproved

    RETURN 0
END



GO
PRINT N'Altering [dbo].[aspnet_UsersInRoles_IsUserInRole]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_UsersInRoles_GetUsersInRoles]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_Membership_GetNumberOfUsersOnline]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
    @ApplicationName            NVARCHAR(256),
    @MinutesSinceLastInActive   INT,
    @TimeZoneAdjustment         INT
AS
BEGIN
    DECLARE @DateActive DATETIME
    SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive + @TimeZoneAdjustment), GETDATE())

    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
PRINT N'Altering [dbo].[aspnet_Users_CreateUser]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_Membership_UpdateUserInfo]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
    @ApplicationName                NVARCHAR(256),
    @UserName                       NVARCHAR(256),
    @IsPasswordCorrect              BIT,
    @UpdateLastLoginActivityDate    BIT,
    @MaxInvalidPasswordAttempts     INT,
    @PasswordAttemptWindow          INT,
    @TimeZoneAdjustment             INT
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

    DECLARE @DateTimeNowUTC DATETIME
    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
    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( @DateTimeNowUTC > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
        BEGIN
            SET @FailedPasswordAttemptWindowStart = @DateTimeNowUTC
            SET @FailedPasswordAttemptCount = 1
        END
        ELSE
        BEGIN
            SET @FailedPasswordAttemptWindowStart = @DateTimeNowUTC
            SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
        END

        BEGIN
            IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
            BEGIN
                SET @IsLockedOut = 1
                SET @LastLockoutDate = @DateTimeNowUTC
            END
        END
    END
    ELSE
    BEGIN
        IF( @UpdateLastLoginActivityDate = 1 )
        BEGIN
            UPDATE  dbo.aspnet_Membership
            SET     LastLoginDate = @DateTimeNowUTC
            WHERE   UserId = @UserId

            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END

            UPDATE  dbo.aspnet_Users
            SET     LastActivityDate = @DateTimeNowUTC
            WHERE   @UserId = UserId

            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END
        END

        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

    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
PRINT N'Altering [dbo].[vw_aspnet_Roles]'
GO

  ALTER 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]
  


GO
PRINT N'Altering [dbo].[aspnet_Users_DeleteUser]'
GO
ALTER 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'aspnet_Membership') AND (type = 'U'))))
    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'aspnet_UsersInRoles') AND (type = 'U'))) )
    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'aspnet_Profile') AND (type = 'U'))) )
    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'aspnet_PersonalizationPerUser') AND (type = 'U'))) )
    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
PRINT N'Altering [dbo].[vw_aspnet_Applications]'
GO

  ALTER VIEW [dbo].[vw_aspnet_Applications]
  AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]
  FROM [dbo].[aspnet_Applications]
  


GO
PRINT N'Altering [dbo].[aspnet_Membership_ResetPassword]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_ResetPassword
    @ApplicationName             NVARCHAR(256),
    @UserName                    NVARCHAR(256),
    @NewPassword                 NVARCHAR(128),
    @MaxInvalidPasswordAttempts  INT,
    @PasswordAttemptWindow       INT,
    @PasswordSalt                NVARCHAR(128),
    @TimeZoneAdjustment          INT,
    @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
            LoweredApplicationName = 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
    DECLARE @DateTimeNowUTC DATETIME
    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT

    UPDATE dbo.aspnet_Membership
    SET    Password = @NewPassword,
           LastPasswordChangedDate = @DateTimeNowUTC,
           PasswordFormat = @PasswordFormat,
           PasswordSalt = @PasswordSalt
    WHERE  @UserId = UserId AND
           ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )

    IF ( @@ROWCOUNT = 0 )
        BEGIN
            IF( @DateTimeNowUTC > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC
                SET @FailedPasswordAnswerAttemptCount = 1
            END
            ELSE
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC
                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
            END
            
            BEGIN
                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                BEGIN
                    SET @IsLockedOut = 1
                    SET @LastLockoutDate = @DateTimeNowUTC
                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
PRINT N'Altering [dbo].[aspnet_Profile_GetNumberOfInactiveProfiles]'
GO

ALTER PROCEDURE dbo.aspnet_Profile_GetNumberOfInactiveProfiles
    @ApplicationName        NVARCHAR(256),
    @ProfileAuthOptions     INT,
    @InactiveSinceDate      DATETIME,
    @TimeZoneAdjustment     INT
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
    BEGIN
        SELECT 0
        RETURN
    END

    IF (@InactiveSinceDate > CONVERT(DATETIME, '17540101', 112) AND  @InactiveSinceDate < CONVERT(DATETIME,'99980101', 112))
        SELECT @InactiveSinceDate = DATEADD(n, -@TimeZoneAdjustment, @InactiveSinceDate)

    SELECT  COUNT(*)
    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
    WHERE   ApplicationId = @ApplicationId
        AND u.UserId = p.UserId
        AND (LastActivityDate <= @InactiveSinceDate)
        AND (
                (@ProfileAuthOptions = 2)
                OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
            )
END



GO
PRINT N'Altering [dbo].[aspnet_Roles_GetAllRoles]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_Membership_FindUsersByEmail]'
GO
ALTER 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
PRINT N'Altering [dbo].[vw_aspnet_Users]'
GO

  ALTER 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]
  


GO
PRINT N'Altering [dbo].[aspnet_Membership_UpdateUser]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_UpdateUser
    @ApplicationName      NVARCHAR(256),
    @UserName             NVARCHAR(256),
    @Email                NVARCHAR(256),
    @Comment              NTEXT,
    @IsApproved           BIT,
    @LastLoginDate        DATETIME,
    @LastActivityDate     DATETIME,
    @UniqueEmail          INT,
    @TimeZoneAdjustment   INT
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_Membership
    SET
         Email            = @Email,
         LoweredEmail     = LOWER(@Email),
         Comment          = @Comment,
         IsApproved       = @IsApproved,
         LastLoginDate    = DATEADD(n, -@TimeZoneAdjustment, @LastLoginDate)
    WHERE
       @UserId = UserId

    IF( @@ERROR <> 0 )
        GOTO Cleanup

    UPDATE dbo.aspnet_Users
    SET
         LastActivityDate = DATEADD(n, -@TimeZoneAdjustment, @LastActivityDate)
    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
PRINT N'Altering [dbo].[aspnet_Membership_UnlockUser]'
GO
ALTER 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
PRINT N'Altering [dbo].[aspnet_Roles_RoleExists]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_Roles_CreateRole]'
GO
ALTER 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
PRINT N'Altering [dbo].[aspnet_Profile_DeleteProfiles]'
GO

ALTER PROCEDURE dbo.aspnet_Profile_DeleteProfiles
    @ApplicationName        NVARCHAR(256),
    @UserNames              NVARCHAR(4000)
AS
BEGIN
    DECLARE @UserName     NVARCHAR(256)
    DECLARE @CurrentPos   INT
    DECLARE @NextPos      INT
    DECLARE @NumDeleted   INT
    DECLARE @DeletedUser  INT
    DECLARE @TranStarted  BIT
    DECLARE @ErrorCode    INT

    SET @ErrorCode = 0
    SET @CurrentPos = 1
    SET @NumDeleted = 0
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    WHILE (@CurrentPos <= LEN(@UserNames))
    BEGIN
        SELECT @NextPos = CHARINDEX(N',', @UserNames,  @CurrentPos)
        IF (@NextPos = 0 OR @NextPos IS NULL)
            SELECT @NextPos = LEN(@UserNames) + 1

        SELECT @UserName = SUBSTRING(@UserNames, @CurrentPos, @NextPos - @CurrentPos)
        SELECT @CurrentPos = @NextPos+1

        IF (LEN(@UserName) > 0)
        BEGIN
            SELECT @DeletedUser = 0
            EXEC dbo.aspnet_Users_DeleteUser @ApplicationName, @UserName, 4, @DeletedUser OUTPUT
            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END
            IF (@DeletedUser <> 0)
                SELECT @NumDeleted = @NumDeleted + 1
        END
    END
    SELECT @NumDeleted
    IF (@TranStarted = 1)
    BEGIN
    	SET @TranStarted = 0
    	COMMIT TRANSACTION
    END
    SET @TranStarted = 0

    RETURN 0

Cleanup:
    IF (@TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    RETURN @ErrorCode
END



GO
PRINT N'Altering [dbo].[aspnet_Profile_DeleteInactiveProfiles]'
GO

ALTER PROCEDURE dbo.aspnet_Profile_DeleteInactiveProfiles
    @ApplicationName        NVARCHAR(256),
    @ProfileAuthOptions     INT,
    @InactiveSinceDate      DATETIME,
    @TimeZoneAdjustment     INT
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
    BEGIN
        SELECT  0
        RETURN
    END

    IF (@InactiveSinceDate > CONVERT(DATETIME, '17540101', 112) AND  @InactiveSinceDate < CONVERT(DATETIME, '99980101', 112))
        SELECT @InactiveSinceDate = DATEADD(n, -@TimeZoneAdjustment, @InactiveSinceDate)

    DELETE
    FROM    dbo.aspnet_Profile
    WHERE   UserId IN
            (   SELECT  UserId
                FROM    dbo.aspnet_Users u
                WHERE   ApplicationId = @ApplicationId
                        AND (LastActivityDate <= @InactiveSinceDate)
                        AND (
                                (@ProfileAuthOptions = 2)
                             OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                             OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
                            )
            )

    SELECT  @@ROWCOUNT
END



GO
PRINT N'Altering [dbo].[aspnet_Profile_SetProperties]'
GO

ALTER PROCEDURE dbo.aspnet_Profile_SetProperties
    @ApplicationName        NVARCHAR(256),
    @PropertyNames          NTEXT,
    @PropertyValuesString   NTEXT,
    @PropertyValuesBinary   IMAGE,
    @UserName               NVARCHAR(256),
    @IsUserAnonymous        BIT,
    @TimeZoneAdjustment     INT
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

    DECLARE @DateTimeNowUTC DATETIME
    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
    DECLARE @UserId UNIQUEIDENTIFIER
    DECLARE @LastActivityDate DATETIME
    SELECT  @UserId = NULL
    SELECT @LastActivityDate = @DateTimeNowUTC

    SELECT @UserId = UserId
    FROM   dbo.aspnet_Users
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
        EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, @IsUserAnonymous, @LastActivityDate, @UserId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF (EXISTS( SELECT *
               FROM   dbo.aspnet_Profile
               WHERE  UserId = @UserId))
        UPDATE dbo.aspnet_Profile
        SET    PropertyNames=@PropertyNames, PropertyValuesString = @PropertyValuesString,
               PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate=@DateTimeNowUTC
        WHERE  UserId = @UserId
    ELSE
        INSERT INTO dbo.aspnet_Profile(UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate)
             VALUES (@UserId, @PropertyNames, @PropertyValuesString, @PropertyValuesBinary, @DateTimeNowUTC)

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    UPDATE dbo.aspnet_Users
    SET    LastActivityDate=@DateTimeNowUTC
    WHERE  UserId = @UserId

    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
PRINT N'Altering [dbo].[aspnet_Membership_GetUserByName]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_GetUserByName
    @ApplicationName      NVARCHAR(256),
    @UserName             NVARCHAR(256),
    @TimeZoneAdjustment   INT,
    @UpdateLastActivity   BIT = 0
AS
BEGIN
    IF (@UpdateLastActivity = 1)
    BEGIN
        DECLARE @DateTimeNowUTC DATETIME
        EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @DateTimeNowUTC
        FROM     dbo.aspnet_Applications a, dbo.aspnet_Users u
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                 u.ApplicationId = a.ApplicationId    AND
                 u.LoweredUserName = LOWER(@UserName)

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1
    END

    SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,m.LastLockoutDate
    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.ApplicationId = a.ApplicationId    AND
            u.LoweredUserName = LOWER(@UserName) AND
            u.UserId = m.UserId
    IF (@@ROWCOUNT = 0) -- Username not found
       RETURN -1

    RETURN 0
END



GO
PRINT N'Altering [dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer]'
GO
ALTER 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
PRINT N'Altering [dbo].[aspnet_Membership_SetPassword]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_SetPassword
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @NewPassword      NVARCHAR(128),
    @PasswordSalt     NVARCHAR(128),
    @TimeZoneAdjustment  INT,
    @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)
    DECLARE @DateTimeNowUTC DATETIME
    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT

    UPDATE dbo.aspnet_Membership
    SET Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt,
        LastPasswordChangedDate = @DateTimeNowUTC
    WHERE @UserId = UserId
    RETURN(0)
END



GO
PRINT N'Altering [dbo].[aspnet_Membership_CreateUser]'
GO
ALTER 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,
    @TimeZoneAdjustment                     INT,
    @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

    IF (@CreateDate IS NULL)
        EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @CreateDate OUTPUT
    ELSE
        SELECT  @CreateDate = DATEADD(n, -@TimeZoneAdjustment, @CreateDate) -- switch TO UTC time

    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

    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 (@NewUserCreated = 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate = @CreateDate
        WHERE  @UserId = UserId
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END

    SELECT @CreateDate = DATEADD( n, @TimeZoneAdjustment, @CreateDate )

    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
PRINT N'Altering [dbo].[aspnet_UsersInRoles_AddUsersToRoles]'
GO

ALTER PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
    @ApplicationName        NVARCHAR(256),
    @UserNames              NVARCHAR(4000),
    @RoleNames              NVARCHAR(4000),
    @TimeZoneAdjustment     INT
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 @TranStarted   BIT
    DECLARE @ErrorCode INT
    SET @ErrorCode   = 0
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0

    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 = @ApplicationId
            IF (@RoleId IS NULL)
            BEGIN
                SELECT @RoleName
                SET @ErrorCode = 2
                GOTO Cleanup
            END

            SELECT @UserId = NULL
            SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
            IF (@UserId IS NULL)
            BEGIN
                DECLARE @DateTimeNowUTC DATETIME
                EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
                EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @DateTimeNowUTC, @UserId OUTPUT
            END

            IF (EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId))
            BEGIN
                SELECT @UserName, @RoleName
                SET @ErrorCode = 3
                GOTO Cleanup
            END
            INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId) VALUES(@UserId, @RoleId)
        END
    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
PRINT N'Altering [dbo].[aspnet_Setup_RemoveAllRoleMembers]'
GO

ALTER 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
PRINT N'Altering [dbo].[aspnet_Setup_RestorePermissions]'
GO

ALTER PROCEDURE [dbo].aspnet_Setup_RestorePermissions
    @name   sysname
AS
BEGIN
    DECLARE @object sysname
    DECLARE @protectType char(10)
    DECLARE @action varchar(20)
    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
PRINT N'Creating index [aspnet_Users_Index] on [dbo].[aspnet_Users]'
GO
CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users] ([ApplicationId], [LoweredUserName])


GO
PRINT N'Creating index [aspnet_Users_Index2] on [dbo].[aspnet_Users]'
GO
CREATE NONCLUSTERED INDEX [aspnet_Users_Index2] ON [dbo].[aspnet_Users] ([ApplicationId], [LastActivityDate])


GO
PRINT N'Creating index [aspnet_UsersInRoles_index] on [dbo].[aspnet_UsersInRoles]'
GO
CREATE NONCLUSTERED INDEX [aspnet_UsersInRoles_index] ON [dbo].[aspnet_UsersInRoles] ([RoleId])

GO
PRINT N'Adding foreign keys to [dbo].[aspnet_Membership]'
GO
ALTER TABLE [dbo].[aspnet_Membership] ADD
CONSTRAINT [FK__aspnet_Me__Appli__0EA330E9] FOREIGN KEY ([ApplicationId]) REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])

Go
Print 'Updating CS Indexes'
DROP INDEX [cs_SearchBarrel].[IX_cs_SearchBarrel_1]
Go

 CREATE  CLUSTERED  INDEX [WordHash_SectionID_CLX_IDX] ON [dbo].[cs_SearchBarrel]([WordHash], [SectionID]) ON [PRIMARY]
GO
 
 CREATE  INDEX [Wordhash_PostID_Weight_IDX] ON [dbo].[cs_SearchBarrel]([WordHash], [PostID], [Weight]) ON [PRIMARY]
GO
 
 CREATE  INDEX [SectionID_IDX] ON [dbo].[cs_SearchBarrel]([SectionID]) ON [PRIMARY]
GO
 
 CREATE  INDEX [ThreadID_SettingsID_IDX] ON [dbo].[cs_SearchBarrel]([ThreadID], [SettingsID]) ON [PRIMARY]
GO
 
 CREATE  INDEX [PostID_SettingsID_IDX] ON [dbo].[cs_SearchBarrel]([PostID], [SettingsID]) ON [PRIMARY]
GO


 CREATE  INDEX [IX_cs_Posts_Thread_Sort] ON [dbo].[cs_Posts]([ThreadID], [SortOrder]) ON [PRIMARY]
GO


--new--
 CREATE NONCLUSTERED INDEX [IX_cs_Posts_SectionID_SettingsID] ON [dbo].[cs_Posts] ([SectionID], [SettingsID]) ON [PRIMARY]
GO

 CREATE CLUSTERED INDEX [IX_cs_TrackedThreads_ThreadID_UserID] ON [dbo].[cs_TrackedThreads] ([ThreadID], [UserID]) ON [PRIMARY]
GO

 CREATE NONCLUSTERED INDEX [IX_cs_Threads_ThreadID_SectionID_UserID] ON [dbo].[cs_Threads] ([SettingsID], [SectionID], [StickyDate]) ON [PRIMARY]
GO
--end new--

 CREATE  INDEX [IX_cs_UserProfile_MembershipID] ON [dbo].[cs_UserProfile]([MembershipID]) ON [PRIMARY]
 Go



ALTER TABLE [dbo].cs_ModerationAudit ADD 
	ID INT IDENTITY CONSTRAINT ID PRIMARY KEY
GO

CREATE INDEX [IX_cs_ModerationAudit_ModeratedOn] ON [dbo].[cs_ModerationAudit]([ModeratedOn])
GO

CREATE NONCLUSTERED INDEX [IX_PostCategories_Path] ON [dbo].[cs_Post_Categories] ([Path])

Alter table cs_Links ALter Column Url nvarchar(255)


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
Print 'Recreating the user view'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_vw_Users_FullUser]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[cs_vw_Users_FullUser]
GO

Create View [dbo].cs_vw_Users_FullUser
as

Select 
	au.UserName, au.IsAnonymous, au.UserId, au.LastActivityDate,
	am.PasswordQuestion, am.Email, am.IsApproved,am.CreateDate, am.LastLoginDate, am.LastPasswordChangedDate, am.IsLockedOut, am.LastLockoutDate,
	am.Comment, cu.UserID as cs_UserID, cu.ForceLogin as cs_ForceLogin, cu.UserAccountStatus as cs_UserAccountStatus, cu.AppUserToken as cs_AppUserToken, am.PasswordFormat,
	cu.LastActivity as cs_LastActivity, cu.LastAction as cs_LastAction,
	fup.TimeZone, fup.TotalPosts, fup.PostSortOrder, fup.PropertyNames as UserPropertyNames, fup.PropertyValues as UserPropertyValues, fup.PostRank, fup.IsAvatarApproved, fup.ModerationLevel, 
	fup.EnableThreadTracking, fup.EnableDisplayUnreadThreadsOnly, fup.EnableAvatar, fup.EnableDisplayInMemberList, fup.EnablePrivateMessages, 
	fup.EnableOnlineStatus, fup.EnableHtmlEmail, fup.SettingsID, ap.PropertyNames as ProfileNames, ap.PropertyValuesString, ap.PropertyValuesBinary
From
	aspnet_Membership am

INNER JOIN aspnet_Users au on am.UserId = au.UserId
INNER JOIN cs_Users cu on au.UserID = cu.MembershipID
INNER JOIN cs_UserProfile fup on cu.UserID = fup.UserID
LEFT  JOIN aspnet_Profile ap on am.UserId = ap.UserId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


--Select * FROM cs_vw_Users_FullUser

grant select on [dbo].[cs_vw_Users_FullUser] to public

GO

ALTER TABLE [dbo].[cs_Posts] ADD
	[PostConfiguration] [int] NOT NULL default(0)

GO

ALTER TABLE [dbo].[cs_Post_Categories] ADD
	[TotalThreads] [int] NOT NULL default(0) ,
	[MostRecentPostDate] [datetime] NULL ,
	[TotalSubThreads] [int] NOT NULL default(0),
	[MostRecentSubPostDate] [datetime] NULL,
	[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_cs_Post_Categories_DateCreated] DEFAULT( getdate() )
GO

declare @UpCategoryID int
declare @UpPath nvarchar(256)
DECLARE Categories_Cursor CURSOR FOR
	SELECT CategoryID FROM cs_Post_Categories

OPEN Categories_Cursor
FETCH NEXT FROM Categories_Cursor INTO @UpCategoryID

WHILE @@FETCH_STATUS = 0
BEGIN

	set @UpPath = (select Path from cs_Post_Categories where CategoryID = @UpCategoryID)

	UPDATE cs_Post_Categories SET
		TotalThreads = (SELECT COUNT(PIC.PostID) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = @UpCategoryID),
		TotalSubThreads = (SELECT COUNT(P.PostID) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = @UpCategoryID OR jC.Path LIKE @UpPath + convert(nvarchar, @UpCategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID),
		MostRecentPostDate = (SELECT MAX(PostDate) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = @UpCategoryID),
		MostRecentSubPostDate = (SELECT MAX(PostDate) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = @UpCategoryID OR jC.Path LIKE @UpPath + convert(nvarchar, @UpCategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID)
	WHERE CategoryID = @UpCategoryID

	FETCH NEXT FROM Categories_Cursor INTO @UpCategoryID
END

CLOSE Categories_Cursor
DEALLOCATE Categories_Cursor
GO



Insert cs_ModerationAction (ModerationAction, Description, TotalActions, SettingsID)
Select 19, 'DeleteSection', 0, SettingsID FROM cs_SiteSettings

GO

ALTER TABLE [dbo].[cs_Smilies] ADD 
	CONSTRAINT [IX_cs_Smilies] UNIQUE  NONCLUSTERED 
	(
		[SmileyCode],
		[SettingsID]
	)  ON [PRIMARY] 
GO


 CREATE  INDEX [IX_cs_Posts_InCategories_PostID] ON [dbo].[cs_Posts_InCategories]([PostID]) ON [PRIMARY]
GO

