installmembership2.sql

community server 源码

--New Roles

exec sp_executesql N'exec sp_addrole ''aspnet_Personalization_BasicAccess'''
exec sp_executesql N'exec sp_addrole ''aspnet_Personalization_FullAccess'''
exec sp_executesql N'exec sp_addrole ''aspnet_Personalization_ReportingAccess'''
exec sp_executesql N'exec sp_addrole ''aspnet_WebEvent_FullAccess'''


set xact_abort on
go

begin transaction
go



--New Tables

create table [dbo].[aspnet_Paths](
  ApplicationId uniqueidentifier not null,
  PathId        uniqueidentifier not null default (newid()) primary key nonclustered,
  Path          nvarchar(256)    not null,
  LoweredPath   nvarchar(256)    not null
)
go

alter table [dbo].[aspnet_Paths] add
  foreign key(ApplicationId) references aspnet_Applications(ApplicationId)
go

create unique clustered index aspnet_Paths_index on aspnet_Paths(ApplicationId,LoweredPath)
go

create table [dbo].[aspnet_PersonalizationAllUsers](
  PathId          uniqueidentifier not null primary key,
  PageSettings    image            not null,
  LastUpdatedDate datetime         not null
)
go

alter table [dbo].[aspnet_PersonalizationAllUsers] add
  foreign key(PathId) references aspnet_Paths(PathId)
go

create table [dbo].[aspnet_PersonalizationPerUser](
  Id              uniqueidentifier not null default (newid()) primary key nonclustered,
  PathId          uniqueidentifier,
  UserId          uniqueidentifier,
  PageSettings    image            not null,
  LastUpdatedDate datetime         not null
)
go

alter table [dbo].[aspnet_PersonalizationPerUser] add
  foreign key(PathId) references aspnet_Paths(PathId),
  foreign key(UserId) references aspnet_Users(UserId)
go

create unique clustered index aspnet_PersonalizationPerUser_index1 on aspnet_PersonalizationPerUser(PathId,UserId)
go

create unique index aspnet_PersonalizationPerUser_ncindex2 on aspnet_PersonalizationPerUser(UserId,PathId)
go

create table [dbo].[aspnet_WebEvent_Events](
  EventId                char(32)       not null primary key,
  EventTimeUtc           datetime       not null,
  EventTime              datetime       not null,
  EventType              nvarchar(256)  not null,
  EventSequence          decimal(19,0)  not null,
  EventOccurrence        decimal(19,0)  not null,
  EventCode              int            not null,
  EventDetailCode        int            not null,
  Message                nvarchar(1024),
  ApplicationPath        nvarchar(256),
  ApplicationVirtualPath nvarchar(256),
  MachineName            nvarchar(256)  not null,
  RequestUrl             nvarchar(1024),
  ExceptionType          nvarchar(256),
  Details                ntext
)
go

commit
go


--Update views

set xact_abort on
go

begin transaction
go

set ansi_nulls on
go

alter VIEW [dbo].[vw_aspnet_Applications]
  AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[LoweredApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]
  FROM [dbo].[aspnet_Applications]
go

grant select on dbo.vw_aspnet_Applications to aspnet_Personalization_ReportingAccess
go


grant select on [dbo].[vw_aspnet_Users] to aspnet_Personalization_ReportingAccess
go

set ansi_nulls on
go

create VIEW [dbo].[vw_aspnet_WebPartState_Paths]
  AS SELECT [dbo].[aspnet_Paths].[ApplicationId], [dbo].[aspnet_Paths].[PathId], [dbo].[aspnet_Paths].[Path], [dbo].[aspnet_Paths].[LoweredPath]
  FROM [dbo].[aspnet_Paths]
go

grant select on [dbo].[vw_aspnet_WebPartState_Paths] to aspnet_Personalization_ReportingAccess
go

set ansi_nulls on
go

create VIEW [dbo].[vw_aspnet_WebPartState_Shared]
  AS SELECT [dbo].[aspnet_PersonalizationAllUsers].[PathId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationAllUsers].[PageSettings]), [dbo].[aspnet_PersonalizationAllUsers].[LastUpdatedDate]
  FROM [dbo].[aspnet_PersonalizationAllUsers]
go

grant select on [dbo].[vw_aspnet_WebPartState_Shared] to aspnet_Personalization_ReportingAccess
go

set ansi_nulls on
go

create VIEW [dbo].[vw_aspnet_WebPartState_User]
  AS SELECT [dbo].[aspnet_PersonalizationPerUser].[PathId], [dbo].[aspnet_PersonalizationPerUser].[UserId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationPerUser].[PageSettings]), [dbo].[aspnet_PersonalizationPerUser].[LastUpdatedDate]
  FROM [dbo].[aspnet_PersonalizationPerUser]
go

grant select on [dbo].[vw_aspnet_WebPartState_User] to aspnet_Personalization_ReportingAccess
go

commit
go

--SPROCS


--aspnet_CheckSchemaVersion

grant execute on [dbo].[aspnet_CheckSchemaVersion] to aspnet_Personalization_BasicAccess
go

grant execute on [dbo].[aspnet_CheckSchemaVersion] to aspnet_Personalization_ReportingAccess
go

grant execute on [dbo].[aspnet_CheckSchemaVersion] to aspnet_WebEvent_FullAccess
go


--DROP OLD aspnet_GetUtcDate

set xact_abort on
go

begin transaction
go

drop procedure aspnet_GetUtcDate
go

commit
go


--aspnet_MembershipCreateUser

set xact_abort on
go

begin transaction
go

set ansi_nulls on
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,
    @CurrentTimeUtc                         datetime,
    @CreateDate                             datetime = NULL,
    @UniqueEmail                            int      = 0,
    @PasswordFormat                         int      = 0,
    @UserId                                 uniqueidentifier OUTPUT
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    DECLARE @NewUserId uniqueidentifier
    SELECT @NewUserId = NULL

    DECLARE @IsLockedOut bit
    SET @IsLockedOut = 0

    DECLARE @LastLockoutDate  datetime
    SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )

    DECLARE @FailedPasswordAttemptCount int
    SET @FailedPasswordAttemptCount = 0

    DECLARE @FailedPasswordAttemptWindowStart  datetime
    SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

    DECLARE @FailedPasswordAnswerAttemptCount int
    SET @FailedPasswordAnswerAttemptCount = 0

    DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime
    SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

    DECLARE @NewUserCreated bit
    DECLARE @ReturnValue   int
    SET @ReturnValue = 0

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

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

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

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

    SET @CreateDate = @CurrentTimeUtc

    SELECT  @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
    IF ( @NewUserId IS NULL )
    BEGIN
        SET @NewUserId = @UserId
        EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
        SET @NewUserCreated = 1
    END
    ELSE
    BEGIN
        SET @NewUserCreated = 0
        IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
        BEGIN
            SET @ErrorCode = 6
            GOTO Cleanup
        END
    END

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

    IF( @ReturnValue = -1 )
    BEGIN
        SET @ErrorCode = 10
        GOTO Cleanup
    END

    IF ( EXISTS ( SELECT UserId
                  FROM   dbo.aspnet_Membership
                  WHERE  @NewUserId = UserId ) )
    BEGIN
        SET @ErrorCode = 6
        GOTO Cleanup
    END

    SET @UserId = @NewUserId

    IF (@UniqueEmail = 1)
    BEGIN
        IF (EXISTS (SELECT *
                    FROM  dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
                    WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
        BEGIN
            SET @ErrorCode = 7
            GOTO Cleanup
        END
    END

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

    INSERT INTO dbo.aspnet_Membership
                ( ApplicationId,
                  UserId,
                  Password,
                  PasswordSalt,
                  Email,
                  LoweredEmail,
                  PasswordQuestion,
                  PasswordAnswer,
                  PasswordFormat,
                  IsApproved,
                  IsLockedOut,
                  CreateDate,
                  LastLoginDate,
                  LastPasswordChangedDate,
                  LastLockoutDate,
                  FailedPasswordAttemptCount,
                  FailedPasswordAttemptWindowStart,
                  FailedPasswordAnswerAttemptCount,
                  FailedPasswordAnswerAttemptWindowStart )
         VALUES ( @ApplicationId,
                  @UserId,
                  @Password,
                  @PasswordSalt,
                  @Email,
                  LOWER(@Email),
                  @PasswordQuestion,
                  @PasswordAnswer,
                  @PasswordFormat,
                  @IsApproved,
                  @IsLockedOut,
                  @CreateDate,
                  @CreateDate,
                  @CreateDate,
                  @LastLockoutDate,
                  @FailedPasswordAttemptCount,
                  @FailedPasswordAttemptWindowStart,
                  @FailedPasswordAnswerAttemptCount,
                  @FailedPasswordAnswerAttemptWindowStart )

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

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

    RETURN 0

Cleanup:

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

    RETURN @ErrorCode

END
go

commit
go


--aspnet_Membership_GetNumberOfUsersOnline

set xact_abort on
go

begin transaction
go

set ansi_nulls on
go

alter PROCEDURE [dbo].[aspnet_Membership_GetNumberOfUsersOnline]
    @ApplicationName            nvarchar(256),
    @MinutesSinceLastInActive   int,
    @CurrentTimeUtc             datetime
AS
BEGIN
    DECLARE @DateActive datetime
    SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)

    DECLARE @NumOnline int
    SELECT  @NumOnline = COUNT(*)
    FROM    dbo.aspnet_Users u(NOLOCK),
            dbo.aspnet_Applications a(NOLOCK),
            dbo.aspnet_Membership m(NOLOCK)
    WHERE   u.ApplicationId = a.ApplicationId                  AND
            LastActivityDate > @DateActive                     AND
            a.LoweredApplicationName = LOWER(@ApplicationName) AND
            u.UserId = m.UserId
    RETURN(@NumOnline)
END
go

commit
go


--aspnet_Membership_GetPassword

set xact_abort on
go

begin transaction
go

set ansi_nulls on
go

alter PROCEDURE [dbo].[aspnet_Membership_GetPassword]
    @ApplicationName                nvarchar(256),
    @UserName                       nvarchar(256),
    @MaxInvalidPasswordAttempts     int,
    @PasswordAttemptWindow          int,
    @CurrentTimeUtc                 datetime,
    @PasswordAnswer                 nvarchar(128) = NULL
AS
BEGIN
    DECLARE @UserId                                 uniqueidentifier
    DECLARE @PasswordFormat                         int
    DECLARE @Password                               nvarchar(128)
    DECLARE @passAns                                nvarchar(128)
    DECLARE @IsLockedOut                            bit
    DECLARE @LastLockoutDate                        datetime
    DECLARE @FailedPasswordAttemptCount             int
    DECLARE @FailedPasswordAttemptWindowStart       datetime
    DECLARE @FailedPasswordAnswerAttemptCount       int
    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime