Delete User? · YAF.NET Support Forum

pinchio
53% (Neutral)
YAF Forumling Topic Starter

18 years ago

Is it possible to delete a user account from YAF? The forum which is located on the asp.net site does not allow for deletion of user accounts which I find troublesome especially in an enterprise setting where you might have to remove accounts for various reasons.

Any thoughts?

pro-logic
50.2% (Neutral)
YAF Developer

18 years ago

#2

You can delete it in the admin section called "users"

Tactical Krav Maga

Zero2Cool
100% (Exalted)
YAF Leader

11 years ago

#3

My previous site had 743 users. To maintain the UserID integrity I had to add some fluff users.

The total user count was 2,229 and I've deleted those users via DNN, however, they are still present in YAF.

What would be the most efficient method of removing those users?

(the fluff users were hardcoded with 'DeleteThis' with a number appended. DeleteThis429)

Would this work? (each fluff user has the name DeleteThis#)

  DELETE FROM [yaf_User] WEHRE [UserName] LIKE '%deletethis%'

Zero2Cool
100% (Exalted)
YAF Leader

11 years ago

#4

Anyway to delete a large amount of users, rather than doing it one by one?

tha_watcha
100% (Exalted)
YAF.NET Project Lead 🤴 YAF Version: 3.0.3

11 years ago

#5

Originally Posted by: Zero2Cool

DELETE FROM [yaf_User] WEHRE [UserName] LIKE '%deletethis%'

This should work, if that is an empty forum.

The Fasted way i would use is using the SQL Server Management Studio and delete the entries all manually in a few seconds.

take a look at the yaf procedure for deleting a yaf user, there are some other Places where you need to delete them

 create procedure [{databaseOwner}].[{objectQualifier}user_delete](@UserID int) as
begin
	
	declare @GuestUserID	int
	declare @UserName		nvarchar(255)
	declare @GuestCount		int

	select @UserName = Name from [{databaseOwner}].[{objectQualifier}User] where UserID=@UserID

	select top 1
		@GuestUserID = a.UserID
	from
		[{databaseOwner}].[{objectQualifier}User] a
		inner join [{databaseOwner}].[{objectQualifier}UserGroup] b on b.UserID = a.UserID
		inner join [{databaseOwner}].[{objectQualifier}Group] c on b.GroupID = c.GroupID
	where
		(c.Flags & 2)<>0

	select 
		@GuestCount = count(1) 
	from 
		[{databaseOwner}].[{objectQualifier}UserGroup] a
		join [{databaseOwner}].[{objectQualifier}Group] b on b.GroupID=a.GroupID
	where
		(b.Flags & 2)<>0

	if @GuestUserID=@UserID and @GuestCount=1 begin
		return
	end

	update [{databaseOwner}].[{objectQualifier}Message] set UserName=@UserName,UserID=@GuestUserID where UserID=@UserID
	update [{databaseOwner}].[{objectQualifier}Topic] set UserName=@UserName,UserID=@GuestUserID where UserID=@UserID
	update [{databaseOwner}].[{objectQualifier}Topic] set LastUserName=@UserName,LastUserID=@GuestUserID where LastUserID=@UserID
	update [{databaseOwner}].[{objectQualifier}Forum] set LastUserName=@UserName,LastUserID=@GuestUserID where LastUserID=@UserID

	delete from [{databaseOwner}].[{objectQualifier}Active] where UserID=@UserID
	delete from [{databaseOwner}].[{objectQualifier}EventLog] where UserID=@UserID	
	delete from [{databaseOwner}].[{objectQualifier}UserPMessage] where UserID=@UserID
	delete from [{databaseOwner}].[{objectQualifier}PMessage] where FromUserID=@UserID AND PMessageID NOT IN (select PMessageID FROM [{databaseOwner}].[{objectQualifier}PMessage])
	-- Delete all the thanks entries associated with this UserID.
	delete from [{databaseOwner}].[{objectQualifier}Thanks] where ThanksFromUserID=@UserID OR ThanksToUserID=@UserID
	-- Delete all the FavoriteTopic entries associated with this UserID.
	delete from [{databaseOwner}].[{objectQualifier}FavoriteTopic] where UserID=@UserID
	-- Delete all the Buddy relations between this user and other users.
	delete from [{databaseOwner}].[{objectQualifier}Buddy] where FromUserID=@UserID   
	delete from [{databaseOwner}].[{objectQualifier}Buddy] where ToUserID=@UserID	 
	-- set messages as from guest so the User can be deleted
	update [{databaseOwner}].[{objectQualifier}PMessage] SET FromUserID = @GuestUserID WHERE FromUserID = @UserID
	delete from [{databaseOwner}].[{objectQualifier}CheckEmail] where UserID = @UserID
	delete from [{databaseOwner}].[{objectQualifier}WatchTopic] where UserID = @UserID
	delete from [{databaseOwner}].[{objectQualifier}WatchForum] where UserID = @UserID
	delete from [{databaseOwner}].[{objectQualifier}UserGroup] where UserID = @UserID
	-- ABOT CHANGED
	-- Delete UserForums entries Too 
	delete from [{databaseOwner}].[{objectQualifier}UserForum] where UserID = @UserID
	delete from [{databaseOwner}].[{objectQualifier}IgnoreUser] where UserID = @UserID OR IgnoredUserID = @UserID
	--END ABOT CHANGED 09.04.2004
	delete from [{databaseOwner}].[{objectQualifier}User] where UserID = @UserID
end
GO

Zero2Cool
100% (Exalted)
YAF Leader

11 years ago

#6

Thank you again. I'll check into this when I get home from work. (port for remote connections is blocked for management studio)

Zero2Cool
100% (Exalted)
YAF Leader

11 years ago

#7

Either I'm not as smart as I think I am, or that's really complicated ... that's basically doing one by one, right?

Edit, the users I need to delete were filler users, meaning, they never logged in, never posted, never received/sent a PM, nothing like that. They are just... there.

DeleteThis2
....
about 1500 more
....
DeleteThis2228

Edit2, it appears I could get away with using the SQL i suggested since they are not in nearly all of those tables mentioned in the delete user stored proc.

Edited by user 11 years ago | Reason: Not specified

Zero2Cool
100% (Exalted)
YAF Leader

11 years ago

#8

I was able to remove them, but it was far more difficult than expected. Thank you for your help. The stored proc helped guide me to the tables necessary to delete the fluff users.

KwangTung
50.2% (Neutral)
YAF Forumling

9 years ago

#9

Originally Posted by: Zero2Cool

DELETE FROM [yaf_User] WEHRE [UserName] LIKE '%deletethis%'

This should work, if that is an empty forum.

The Fasted way i would use is using the SQL Server Management Studio and delete the entries all manually in a few seconds.

take a look at the yaf procedure for deleting a yaf user, there are some other Places where you need to delete them

 create procedure [{databaseOwner}].[{objectQualifier}user_delete](@UserID int) as
begin
	
	declare @GuestUserID	int
	declare @UserName		nvarchar(255)
	declare @GuestCount		int

	select @UserName = Name from [{databaseOwner}].[{objectQualifier}User] where UserID=@UserID

	select top 1
		@GuestUserID = a.UserID
	from
		[{databaseOwner}].[{objectQualifier}User] a
		inner join [{databaseOwner}].[{objectQualifier}UserGroup] b on b.UserID = a.UserID
		inner join [{databaseOwner}].[{objectQualifier}Group] c on b.GroupID = c.GroupID
	where
		(c.Flags & 2)<>0

	select 
		@GuestCount = count(1) 
	from 
		[{databaseOwner}].[{objectQualifier}UserGroup] a
		join [{databaseOwner}].[{objectQualifier}Group] b on b.GroupID=a.GroupID
	where
		(b.Flags & 2)<>0

	if @GuestUserID=@UserID and @GuestCount=1 begin
		return
	end

	update [{databaseOwner}].[{objectQualifier}Message] set UserName=@UserName,UserID=@GuestUserID where UserID=@UserID
	update [{databaseOwner}].[{objectQualifier}Topic] set UserName=@UserName,UserID=@GuestUserID where UserID=@UserID
	update [{databaseOwner}].[{objectQualifier}Topic] set LastUserName=@UserName,LastUserID=@GuestUserID where LastUserID=@UserID
	update [{databaseOwner}].[{objectQualifier}Forum] set LastUserName=@UserName,LastUserID=@GuestUserID where LastUserID=@UserID

	delete from [{databaseOwner}].[{objectQualifier}Active] where UserID=@UserID
	delete from [{databaseOwner}].[{objectQualifier}EventLog] where UserID=@UserID	
	delete from [{databaseOwner}].[{objectQualifier}UserPMessage] where UserID=@UserID
	delete from [{databaseOwner}].[{objectQualifier}PMessage] where FromUserID=@UserID AND PMessageID NOT IN (select PMessageID FROM [{databaseOwner}].[{objectQualifier}PMessage])
	-- Delete all the thanks entries associated with this UserID.
	delete from [{databaseOwner}].[{objectQualifier}Thanks] where ThanksFromUserID=@UserID OR ThanksToUserID=@UserID
	-- Delete all the FavoriteTopic entries associated with this UserID.
	delete from [{databaseOwner}].[{objectQualifier}FavoriteTopic] where UserID=@UserID
	-- Delete all the Buddy relations between this user and other users.
	delete from [{databaseOwner}].[{objectQualifier}Buddy] where FromUserID=@UserID   
	delete from [{databaseOwner}].[{objectQualifier}Buddy] where ToUserID=@UserID	 
	-- set messages as from guest so the User can be deleted
	update [{databaseOwner}].[{objectQualifier}PMessage] SET FromUserID = @GuestUserID WHERE FromUserID = @UserID
	delete from [{databaseOwner}].[{objectQualifier}CheckEmail] where UserID = @UserID
	delete from [{databaseOwner}].[{objectQualifier}WatchTopic] where UserID = @UserID
	delete from [{databaseOwner}].[{objectQualifier}WatchForum] where UserID = @UserID
	delete from [{databaseOwner}].[{objectQualifier}UserGroup] where UserID = @UserID
	-- ABOT CHANGED
	-- Delete UserForums entries Too 
	delete from [{databaseOwner}].[{objectQualifier}UserForum] where UserID = @UserID
	delete from [{databaseOwner}].[{objectQualifier}IgnoreUser] where UserID = @UserID OR IgnoredUserID = @UserID
	--END ABOT CHANGED 09.04.2004
	delete from [{databaseOwner}].[{objectQualifier}User] where UserID = @UserID
end
GO

I use the trigger for call this store procedure and it show execute (1 row(s) affected) but the user data isn't remove from database

Important Information: