cs_forums_reassignanddeleteuser.prc

解压即可使用

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


Create Proc [dbo].cs_Forums_ReAssignAndDeleteUser

(
	@CurrentUserName nvarchar(256),
	@NewUserName nvarchar(256),
	@SettingsID int
)

as

Declare @CUserID int
Declare @NUserID int
Declare @CGUserID uniqueidentifier
Declare @NGUserID uniqueidentifier

Declare @ApplicationID uniqueidentifier


--Find the Application
Select @ApplicationID = ApplicationID FROM aspnet_applications where ApplicationName in (Select ApplicationName FROM cs_SiteSettings where SettingsID = @SettingsID)

if(@ApplicationID is null)
Return -1

--Find the Guid UserID's
Select @CGUserID = UserID FROM aspnet_Users where  ApplicationID = @ApplicationID and UserName = @CurrentUserName
Select @NGUserID = UserID FROM aspnet_Users where  ApplicationID = @ApplicationID and UserName = @NewUserName

if(@CGUserID is null)
Return -2

if(@NGUserID is null)
Return -3

--Find the int UserID's
Select @CUserID = UserID FROM cs_UserProfile where MembershipID = @CGUserID and SettingsID = @SettingsID
Select @NUserID = UserID FROM cs_UserProfile where MembershipID = @NGUserID and SettingsID = @SettingsID

if(@CUserID is null)
Return -4

if(@NUserID is null)
Return -5

-- ReAssign Content

update cs_ModerationAudit
Set UserID = @NUserID 
where UserID = @CUserID

update cs_PostAttachments
Set UserID = @NUserID
where UserID = @CUserID


update cs_Posts
Set UserID = @NUserID, PostAuthor = convert(nvarchar(64), @NewUserName)
where UserID = @CUserID

update cs_PrivateMessages
Set UserID = @NUserID
where UserID = @CUserID

update cs_Threads
Set UserID = @NUserID, PostAuthor = convert(nvarchar(64), @NewUserName)
where UserID = @CUserID

update cs_Threads
Set MostRecentPostAuthorID = @NUserID, MostRecentPostAuthor = convert(nvarchar(64), @NewUserName)
where MostRecentPostAuthorID = @CUserID

update cs_Sections
Set MostRecentPostAuthorID = @NUserID, MostRecentPostAuthor = convert(nvarchar(64), @NewUserName)
where MostRecentPostAuthorID = @CUserID

-- Delete Content

Delete FROM cs_PostRating where UserID = @CUserID
Delete FROM cs_SectionsRead where UserID = @CUserID
Delete FROM cs_statistics_User where UserID = @CUserID
Delete FROM cs_TrackedSections where UserID = @CUserID
Delete FROM cs_ThreadsRead where UserID = @CUserID
Delete FROM cs_UserAvatar where UserID = @CUserID
Delete FROM cs_Votes where UserID = @CUserID
Delete FROM cs_UserProfile where UserID = @CUserID

Delete FROM aspnet_UsersInRoles where UserID = @CGUserID
Delete FROM aspnet_Profile where UserID = @CGUserID
Delete FROM aspnet_Membership where UserID = @CGUserID
Delete FROM aspnet_Users where UserID = @CGUserID


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