cs_system_createcommunity.prc

解压即可使用

	)
Select @DeletedSectionID = @@Identity
Print 'Created Deleted Forum'

if(@IsApplicationCreated = 1)
Begin

	print 'Created default roles...'

	declare @EveryoneRoleID uniqueidentifier
	Set @EveryoneRoleID = newID()
	

	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (@EveryoneRoleID, @ApplicationID, N'Everyone', N'everyone', N'Do not add users to this role. This role exists only for permission mapping. All anonymous and registered users are automatically part of this role.')
	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N'Registered Users', N'Registered Users', N'All registered users for all sites are automatically added to this role.')
	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N'Trusted Users', N'Trusted Users', N'Trusted users are allowed to use additional capabilities of the site.')
	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N'Editor', N'editor', N'desc')
	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N'Moderator', N'moderator', N'moderator')
	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N'GalleryAdministrator', N'galleryadministrator', NULL)
	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N'ForumsAdministrator', N'forumsadministrator', NULL)
	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N'BlogAdministrator', N'blogadministrator', NULL)
	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N'SystemAdministrator', N'systemadministrator', NULL)	

	Declare @UserID uniqueidentifier
	Declare @cs_UserID int
	
	if(@AdminUserName is not null)
	Begin
		if(@AdminPassword is null)
		Begin
			Set @AdminPassword = '/u9vGm1ml3IEmCwECl79L73nyXE='
			Set @PasswordFormat = 1
		End 
	
	Set @UserID = newid()
	
	Print 'Create Admin User'
	INSERT INTO [aspnet_Users] ([UserId], [ApplicationId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate]) 
	      VALUES (@UserID, @ApplicationID, @AdminUserName, Lower(@AdminUserName), NULL, 0, getdate())
	--Do we need to make Salt configurable?
	INSERT INTO [aspnet_Membership] ([ApplicationID], [UserId], [Password],     [PasswordFormat], [PasswordSalt],              [MobilePIN], [Email], [LoweredEmail], [PasswordQuestion], [PasswordAnswer], [IsApproved], [CreateDate], [LastLoginDate], [LastPasswordChangedDate], [Comment], FailedPasswordAnswerAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAttemptCount, LastLockoutDate, IsLockedOut) 
	                         VALUES (@ApplicationID, @UserID,  @AdminPassword, @PasswordFormat,   N'DVZTktxeMzDtXR7eik7Cdw==', NULL,        '[e-mail]',    NULL,            NULL,               NULL,            1,            getdate(),    getdate(),       getdate(),                 NULL,                 '1753-01-01',                              0,                               '1753-01-01',                       0,                          '1753-01-01',      0)
	
	INSERT INTO [cs_Users]([MembershipID], [ForceLogin], [UserAccountStatus], [AppUserToken], [LastActivity], [LastAction])
	VALUES(@UserID, 0,1, null, getdate(), '')


	Set @cs_UserID = @@Identity
	
	Print 'Create Anonymous User'
	INSERT INTO [cs_UserProfile]([UserID], [TimeZone], [TotalPosts], [PostSortOrder], [PostRank], [IsAvatarApproved], [ModerationLevel], [EnableThreadTracking], [EnableDisplayUnreadThreadsOnly], [EnableAvatar], [EnableDisplayInMemberList], [EnablePrivateMessages], [EnableOnlineStatus], [EnableHtmlEmail], [MembershipID], [SettingsID], [PropertyNames], [PropertyValues])
	VALUES (@cs_UserID, 0, 0, 0, 0x01, 0, 0, 0, 0, 0, 0, 0, 1, 0, @UserID, @SettingsID, NULL, NULL)
	
	Print 'Created Admin User'
	
	Insert aspnet_UsersInRoles (UserId, RoleId) 
	Select @UserID, RoleId FROM aspnet_Roles where ApplicationId = @ApplicationID
	
	End
	
	
	Set @UserID = newid()
	
	INSERT INTO [aspnet_Users] ([UserId], [ApplicationId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate]) 
	      VALUES (@UserID, @ApplicationID, 'Anonymous', Lower('Anonymous'), NULL, 1, getdate())
	--Do we need to make Salt configurable?
	INSERT INTO [aspnet_Membership] ([ApplicationID],[UserId], [Password],     [PasswordFormat], [PasswordSalt],              [MobilePIN], [Email], [LoweredEmail], [PasswordQuestion], [PasswordAnswer], [IsApproved], [CreateDate], [LastLoginDate], [LastPasswordChangedDate], [Comment],FailedPasswordAnswerAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAttemptCount, LastLockoutDate, IsLockedOut) 
	                         VALUES (@ApplicationID, @UserID,  @UserID,         0,               N'DVZTktxeMzDtXR7eik7Cdw==', NULL,        NULL,    NULL,            NULL,               NULL,            1,            getdate(),    getdate(),       getdate(),                 NULL,      '1753-01-01',                              0,                               '1753-01-01',                       0,                          '1753-01-01',      0)
	
	INSERT INTO [cs_Users]([MembershipID], [ForceLogin], [UserAccountStatus], [AppUserToken], [LastActivity], [LastAction])
	VALUES(@UserID, 0,1, null, getdate(), '')

	Set @cs_UserID = @@Identity
	
	INSERT INTO [cs_UserProfile]([UserID], [TimeZone], [TotalPosts], [PostSortOrder], [PostRank], [IsAvatarApproved], [ModerationLevel], [EnableThreadTracking], [EnableDisplayUnreadThreadsOnly], [EnableAvatar], [EnableDisplayInMemberList], [EnablePrivateMessages], [EnableOnlineStatus], [EnableHtmlEmail], [MembershipID], [SettingsID], [PropertyNames], [PropertyValues])
	VALUES                          (@cs_UserID, 0, 0, 0, 0x01, 0, 0, 0, 0, 0, 0, 0, 1, 0, @UserID, @SettingsID, NULL, NULL)

	-- add the anonymous user to the everyone role
	INSERT INTO aspnet_UsersInRoles ([UserId], [RoleId]) VALUES( @UserID, @EveryoneRoleID )
END
ELSE
BEGIN
	Print 'Copied CS_UserProfile'
	INSERT INTO [cs_UserProfile]([UserID], [TimeZone], [TotalPosts], [PostSortOrder], [PostRank], [IsAvatarApproved], [ModerationLevel], [EnableThreadTracking], [EnableDisplayUnreadThreadsOnly], [EnableAvatar], [EnableDisplayInMemberList], [EnablePrivateMessages], [EnableOnlineStatus], [EnableHtmlEmail], [MembershipID], [SettingsID], [PropertyNames], [PropertyValues])
				Select    UserID,  TimeZone,    0,            PostSortOrder,                   0,          0,                 0,                 [EnableThreadTracking],  [EnableDisplayUnreadThreadsOnly],  0,             [EnableDisplayInMemberList], [EnablePrivateMessages], [EnableOnlineStatus], [EnableHtmlEmail], [MembershipID], @SettingsID,  null, null
				FROM cs_UserProfile up, 
				    (Select Top 1 SettingsID, ApplicationName FROM cs_SiteSettings where SettingsID <> @SettingsID and ApplicationName = @ApplicationName order by SettingsID desc) ss 
				where up.SettingsID = ss.SettingsID 
End	

Declare @Perm_RoleID uniqueidentifier

	-- These items need to be set outside the if(@IsApplicationCreated = 1) since they need to be created based on the SettingsID and not ApplicationID


	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'Everyone'
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 0, @Perm_RoleID, 0x000000000000000F, 0x00001007000000F0 ) -- Forum Permission
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 1, @Perm_RoleID, 0x0000000000000009, 0x0000000000000004 ) -- Weblog Permission  >> Allow View, Reply  Deny Post
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 2, @Perm_RoleID, 0x0000000000000089, 0xFFFFFFFFFFFFFF76 ) -- Gallery Permission
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 3, @Perm_RoleID, 0x0000000000000001, 0x000000000000000C ) -- GuestBook Permission  >> Allow View,  Deny Post, Reply
	-- SET SPECIFIC DELETED adn Reporting Forums Permission	
	INSERT INTO [cs_SectionPermissions] ([SettingsID], [SectionID], [RoleID], [AllowMask], [DenyMask]) values( @SettingsID, @ReportingSectionID, @Perm_RoleID, 0x0, -1 )
	INSERT INTO [cs_SectionPermissions] ([SettingsID], [SectionID], [RoleID], [AllowMask], [DenyMask]) values( @SettingsID, @DeletedSectionID, @Perm_RoleID, 0x0, -1 )



	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'Registered Users'
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 0, @Perm_RoleID, 0x00000000000000BF, 0 )
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 1, @Perm_RoleID, 0x0000000000000009, 0x0000000000000004 ) -- Weblog Permission  >> Allow View, Reply  Deny Post
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 2, @Perm_RoleID, 0x00000000000000B9, 0 ) -- Gallery Permission

	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 3, @Perm_RoleID, 0x0000000000000009, 0x0000000000000004 ) -- GuestBook Permission  >> Allow View, Reply  Deny Post
	-- SET SPECIFIC DELETED adn Reporting Forums Permission	
	INSERT INTO [cs_SectionPermissions] ([SettingsID], [SectionID], [RoleID], [AllowMask], [DenyMask]) values( @SettingsID, @ReportingSectionID, @Perm_RoleID, 0x0, -1 )
	INSERT INTO [cs_SectionPermissions] ([SettingsID], [SectionID], [RoleID], [AllowMask], [DenyMask]) values( @SettingsID, @DeletedSectionID, @Perm_RoleID, 0x0, -1 )
	
	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'Editor'
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 0, @Perm_RoleID, 0x0000000F000000FF, 0 )

	-- SET SPECIFIC DELETED Forums Permission
	INSERT INTO [cs_SectionPermissions] ([SettingsID], [SectionID], [RoleID], [AllowMask], [DenyMask]) values( @SettingsID, @DeletedSectionID, @Perm_RoleID, 0x0, -1 )

	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'Moderator'
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 0, @Perm_RoleID, 0x0000100F000000FF, 0 )

	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'GalleryAdministrator'
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 2, @Perm_RoleID, 0x01001000000000BD, 0 ) -- Gallery Permission

	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'ForumsAdministrator'
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 0, @Perm_RoleID, 0x0100100F000000FF, 0 )

	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'BlogAdministrator'
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 1, @Perm_RoleID, 0x000000000000000D, 0 ) -- Weblog Permission  >> Allow View, Reply, Post


	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'SystemAdministrator'
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 0, @Perm_RoleID, 0x4100100F000000FF, 0 )
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 1, @Perm_RoleID, 0x000000000000000D, 0 ) -- Weblog Permission >> Allow View, Reply, Post
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 2, @Perm_RoleID, 0x41001000000000BD, 0 ) -- Gallery Permission

---Return New Data	

if @CreateSamples = 1
Begin
	--Not working yet
	exec cs_system_SampleGallery @SettingsID, @AdminUserName
	exec cs_system_SampleWeblog @SettingsID, @AdminUserName
End

--Select * FROM cs_Sites
--DELETE FROM cs_Sites where ApplicationID > 50
Select * FROM aspnet_Applications where ApplicationId = @ApplicationID
Select * FROM aspnet_Users where ApplicationId = @ApplicationID
Select * FROM aspnet_Membership where UserId in (Select UserId FROM aspnet_Users where ApplicationId = @ApplicationID)
Select * FROM cs_UserProfile where SettingsID = @SettingsID
Select * FROM cs_Sites where SiteID = @SiteID
Select * FROM cs_SiteSettings where SettingsID = @SettingsID
Select * FROM cs_SiteMappings where SettingsID = @SettingsID and SiteID = @SiteID
Select * FROM cs_ProductPermissions where SettingsID = @SettingsID
Select * FROM cs_SectionPermissions where SettingsID = @SettingsID

go


grant execute on [dbo].[cs_system_CreateCommunity] to public
go