cs_schema_patch_20050420.sql

解压即可使用

/*   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

After this is done, you need to update cs_vw_FullUser before updating all sprocs!

*/

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]

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




Create TABLE [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 (''),
	
)        


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

/*  If you do not have an ApplicationID column on the aspnet_Membership Table
Alter table [dbo].[aspnet_Membership] Add [ApplicationID] UNIQUEIDENTIFIER

Update aspnet_Membership 
Set [ApplicationID] = aspnet_Users.ApplicationID
From aspnet_Users
where aspnet_Membership.UserID = aspnet_Users.UserID

Alter table [dbo].[aspnet_Membership] Alter Column [ApplicationID] UNIQUEIDENTIFIER NOT NULL
*/

ALTER TABLE [dbo].[aspnet_Membership] ADD Constraint [FK_aspnet_Applications] FOREIGN KEY
	(
		[ApplicationId]
	) REFERENCES [aspnet_Applications] (
		[ApplicationId]
	)
GO

ALTER TABLE [dbo].[aspnet_Membership] ADD Constraint [PK_aspnet_Membership]  PRIMARY KEY  NONCLUSTERED 
	(
		[UserId]
	)  ON [PRIMARY] 
GO

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


ALTER TABLE [dbo].[aspnet_Membership] DROP CONSTRAINT [DF_aspnet_membership_ForceLogin]
ALTER TABLE [dbo].[aspnet_Membership] DROP CONSTRAINT [DF_aspnet_membership_LastActivity]
ALTER TABLE [dbo].[aspnet_Membership] DROP CONSTRAINT [DF_aspnet_membership_LastAction]
ALTER TABLE [dbo].[aspnet_Membership] DROP CONSTRAINT [DF_aspnet_membership_Approved]

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



Alter Table [dbo].[aspnet_Membership] Drop Column cs_UserID

Alter Table [dbo].[aspnet_Membership] Drop Column cs_ForceLogin

Alter table [dbo].[aspnet_Membership] DROP Column cs_LastActivity

Alter table [dbo].[aspnet_Membership] DROP column cs_LastAction

Alter table [dbo].[aspnet_Membership] DROP column cs_AppUserToken
Alter table [dbo].[aspnet_Membership] DROP column cs_UserAccountStatus



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAnonymousUserID]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetAnonymousUserID]
GO