siteserver_procedures.sql

一个真正的CMS系统,可以完全实现建站自动化,用.NET开发.

if exists (select * from dbo.sysobjects where id = object_id(N'[siteserver_system_MemberRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [siteserver_system_MemberRole]
GO

CREATE Proc siteserver_system_MemberRole
(
	@ApplicationName nvarchar(256),
	@ConsoleEmail nvarchar(256),
	@ConsoleUserName nvarchar(256),
	@ConsolePassword nvarchar(256),
	@PasswordFormat int = 0
)

as

Declare @ApplicationID uniqueidentifier
Declare @ConsoleUserID uniqueidentifier
Declare @SystemUserID uniqueidentifier
DECLARE @Version nvarchar(64)
DECLARE @IsApplicationCreated bit
DECLARE @AdminRoleID uniqueidentifier

Set @IsApplicationCreated = 0

Set @Version = null

--Get or Create the Application ID.
--An ApplicationName/ID can exist in more than one site, so it does not need to be unique
Set @ApplicationID = null
Select @ApplicationID = ApplicationId FROM aspnet_Applications where  LoweredApplicationName = Lower(@ApplicationName)

if(@ApplicationID is  null)
BEGIN
	exec aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId = @ApplicationID OUTPUT
	Set @IsApplicationCreated = 1
	Print 'Created New Application ' + @ApplicationName + '(' + Convert(varchar(36),@ApplicationID) + ')'

END
ELSE
BEGIN
	SET @IsApplicationCreated = 0
END

if(@IsApplicationCreated = 1)
Begin

	print 'Created default roles...'

	declare @EveryoneRoleID uniqueidentifier
	declare @RegisteredUserRoleID uniqueidentifier
	declare @ConsoleAdministratorRoleID uniqueidentifier
	Set @EveryoneRoleID = newID()
	Set @RegisteredUserRoleID = newID()
	Set @ConsoleAdministratorRoleID = newID()
	

	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (@EveryoneRoleID, @ApplicationID, N'Everyone', N'everyone', N'所有访问者包括管理员、注册用户和匿名用户都属于此角色。')
	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (@RegisteredUserRoleID, @ApplicationID, N'RegisteredUser', N'registereduser', N'已注册用户自动属于此角色。')
	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N'Administrator', N'administrator', N'后台管理员角色，属于此角色的用户能够登录后台系统。')
	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N'SystemAdministrator', N'systemadministrator', N'系统管理员，拥有管理站点所需的所有权限。')
	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (@ConsoleAdministratorRoleID, @ApplicationID, N'ConsoleAdministrator', N'consoleadministrator', N'总控制管理员，拥有所有权限。')

	Declare @ConsoleAdministratorUserID uniqueidentifier
	
	if(@ConsoleUserName is not null)
	Begin

		Set @ConsoleAdministratorUserID = newid()
		
		Print 'Create Console User'
		INSERT INTO [aspnet_Users] ([UserId], [ApplicationId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate]) 
		      VALUES (@ConsoleAdministratorUserID, @ApplicationID, @ConsoleUserName, Lower(@ConsoleUserName), 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, @ConsoleAdministratorUserID,  @ConsolePassword, @PasswordFormat,   N'DVZTktxeMzDtXR7eik7Cdw==', NULL,       @ConsoleEmail,    NULL,            NULL,               NULL,            1,            getdate(),    getdate(),       getdate(),                 NULL,                 '1753-01-01',                              0,                               '1753-01-01',                       0,                          '1753-01-01',      0)
		
		Print 'Add Console User To Roles'
		
		Insert aspnet_UsersInRoles (UserId, RoleId) values (@ConsoleAdministratorUserID, @RegisteredUserRoleID)
		Insert aspnet_UsersInRoles (UserId, RoleId) values (@ConsoleAdministratorUserID, @ConsoleAdministratorRoleID)
	
	End
	
	Declare @AnonymousUserID uniqueidentifier
	Set @AnonymousUserID = newid()
	
	Print 'Create Anonymous User'
	INSERT INTO [aspnet_Users] ([UserId], [ApplicationId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate]) 
	      VALUES (@AnonymousUserID, @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, @AnonymousUserID,  @AnonymousUserID,         0,               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)

	-- add the anonymous user to the everyone role
	INSERT INTO aspnet_UsersInRoles ([UserId], [RoleId]) VALUES( @AnonymousUserID, @EveryoneRoleID )
END
GO