cs_1.0_to_1.1_upgrade.sql

解压即可使用

    	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