Deleting all members and member data Options
LostInSwiss
Posted: Friday, November 23, 2007 1:55:25 AM

Rank: Devotee

Joined: 3/31/2007
Posts: 64
Location: Switzerland
I Just did this tonight and it might be hapfull to somone somwhere so I thought I would post it.

So you want to delete all members and all their data?
Run the SQL below:

I have tested this on v3 and found no issues but take no responsabllity for damages so backup your DB first.

Code:
begin transaction

IF OBJECT_ID('tempdb..#NEWNODES') IS NOT NULL DROP TABLE #NEWNODES
CREATE    TABLE #NEWNODES
    (
    MEMBNODEID INT
    )
insert into #NEWNODES
    (MEMBNODEID)
select NODEID
from dbo.cmsMember

--delete all MEMBER contentVersions
delete from [cmsContentVersion]
where VersionId
    in (
    select versionId
    from cmsPropertyData
    where contentnodeid
        in (select MEMBNODEID from #NEWNODES)
    )

--DELETE MEMBER CONTENT
delete  FROM [dbo].[cmsContent]
where [nodeId]
        in (select MEMBNODEID from #NEWNODES)

--delete MEMBER property data
delete  from cmsPropertyData
where contentnodeid
    in (select MEMBNODEID from #NEWNODES)

--delete member type asignments
truncate table [dbo].[cmsMemberType]

--DELETE MEMBER GROUP ASIGNMENTS
truncate table dbo.cmsMember2MemberGroup

--DELETE MEMBERS
truncate table dbo.cmsMember

--DELETE MEMBER NODES
delete from dbo.umbracoNode
where id
    in (select MEMBNODEID from #NEWNODES)

delete from [dbo].[umbracoNode]
where [nodeObjectType]='366E63B9-880F-4E13-A61C-98069B029728 '

--If you run this an get no errors then exicute a commit transaction
LostInSwiss
Posted: Friday, November 23, 2007 1:56:28 AM

Rank: Devotee

Joined: 3/31/2007
Posts: 64
Location: Switzerland
Sorry about the spelling :D
kalpa
Posted: Wednesday, April 02, 2008 2:38:25 PM

Rank: Fanatic

Joined: 7/19/2006
Posts: 441
Location: Göteborg, Sweden
Never mind the spelling, this script made my day!! ; )

Thanks a lot Alec!

In my opinion Umbraco could really need some updates in the member section, easier handling of multiple ones is absolutely one of these...

Removing the A-Z folders is another, but I guess they are there to save some DB requests...

In fact, an Advanced Member Dashboard is an good idea... ; )

I'll add this to Codeplex...

// ; ) Kalle

" - Yeah I'd like to share your point of view, as long as it's my view too... (http://www.d-a-d.dk/lyrics/pointofview)
kalpa
Posted: Wednesday, April 02, 2008 2:50:01 PM

Rank: Fanatic

Joined: 7/19/2006
Posts: 441
Location: Göteborg, Sweden
Only thing with the script:

It also deleted all my Member Groups, but I only had two so that's no problem but it might be good for someone else to know BEFORE running it... ; )

" - Yeah I'd like to share your point of view, as long as it's my view too... (http://www.d-a-d.dk/lyrics/pointofview)
kalpa
Posted: Wednesday, April 02, 2008 3:45:07 PM

Rank: Fanatic

Joined: 7/19/2006
Posts: 441
Location: Göteborg, Sweden
And finally the lines that deletes the MemberTypes are the last two ones:

--delete from [dbo].[umbracoNode]
--where [nodeObjectType]='366E63B9-880F-4E13-A61C-98069B029728'

Uncomment them like in the example above to keep your Member Groups...

// ; ) Kalle

" - Yeah I'd like to share your point of view, as long as it's my view too... (http://www.d-a-d.dk/lyrics/pointofview)
Users browsing this topic
Guest


You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.