Dear Sebastian, If following Query is that one, I try but I got an Error:
/* Remove All Deleted Users - Version 0.9.4 (2014-09-10)
================================================================================
(c) Sebastian Leupold, dnnWerk/gamma concept mbH 2014
Run this Script to create and execute a procedure to finally hard delete users.
== Please make sure to use latest version from
http://dnnscript.codeplex.com ===
Instructions:
=============
- Install by running as script from SQL item in Host menu inside DNN or run in
SQL Server Management Studio, after replacing placeholders {databaseOwner} and
{objectQualifier} by its proper values from web.config file.
- Make sure that the currently used account is member of dbOwner database role.
License and Disclaimer:
=======================
Published under Microsoft Open Source Reciprocal License (Ms-RL). For details,
please read
http://dnnscript.codeplex.com/license.
Feel free to use this script as you need, but there is no warranty or liability
for any damage or effort, eventually been caused.
Please report issues at
https://dnnscript.codeplex.com/WorkIt... ================================================================================
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'vitalitasaloon_ae_[dbo.RemoveAllDeletedUsers]') AND type in (N'P', N'PC'))
DROP PROCEDURE vitalitasaloon_ae_[dbo.RemoveAllDeletedUsers]
GO
CREATE PROCEDURE vitalitasaloon_ae_[dbo.RemoveAllDeletedUsers]
@PortalID Int -- pass Null|-1 to delete from all portals
AS
BEGIN
-- delete individual permission of deleted users
DELETE FROM vitalitasaloon_ae_[dbo.DesktopModulePermission]
FROM vitalitasaloon_ae_[dbo.DesktopModulePermission] P
JOIN vitalitasaloon_ae_[dbo.UserPortals] U ON P.UserID = U.UserID AND (U.PortalID = @PortalID OR IsNull(@PortalID, -1) = -1)
JOIN vitalitasaloon_ae_[dbo.PortalDesktopModules] M ON P.PortalDesktopModuleID = M.PortalDesktopModuleID AND M.PortalID = U.PortalID
WHERE U.isDeleted = 1;
DELETE FROM vitalitasaloon_ae_[dbo.FolderPermission]
FROM vitalitasaloon_ae_[dbo.FolderPermission] P
JOIN vitalitasaloon_ae_[dbo.UserPortals] U ON P.UserID = U.UserID AND (U.PortalID = @PortalID OR IsNull(@PortalID, -1) = -1)
JOIN vitalitasaloon_ae_[dbo.Folders] F ON P.FolderID = F.FolderID AND F.PortalID = U.PortalID
WHERE U.isDeleted = 1;
DELETE FROM vitalitasaloon_ae_[dbo.ModulePermission]
FROM vitalitasaloon_ae_[dbo.ModulePermission] P
JOIN vitalitasaloon_ae_[dbo.UserPortals] U ON P.UserID = U.UserID AND (U.PortalID = @PortalID OR IsNull(@PortalID, -1) = -1)
JOIN vitalitasaloon_ae_[dbo.Modules] M ON P.ModuleID = M.ModuleID AND M.PortalID = U.PortalID
WHERE U.isDeleted = 1;
DELETE FROM vitalitasaloon_ae_[dbo.TabPermission]
FROM vitalitasaloon_ae_[dbo.TabPermission] P
JOIN vitalitasaloon_ae_[dbo.UserPortals] U ON P.UserID = U.UserID AND (U.PortalID = @PortalID OR IsNull(@PortalID, -1) = -1)
JOIN vitalitasaloon_ae_[dbo.Tabs] T ON P.TabID = T.TabID AND T.PortalID = U.PortalID
WHERE U.isDeleted = 1;
-- delete profile
DELETE FROM vitalitasaloon_ae_[dbo.UserProfile]
FROM vitalitasaloon_ae_[dbo.UserProfile] P
JOIN vitalitasaloon_ae_[dbo.UserPortals] U ON P.UserID = U.UserID AND (U.PortalID = @PortalID OR IsNull(@PortalID, -1) = -1)
JOIN vitalitasaloon_ae_[dbo.ProfilePropertyDefinition] D ON P.PropertyDefinitionID = D.PropertyDefinitionID AND D.PortalID = U.PortalID
WHERE U.isDeleted = 1;
-- delete messages
DELETE FROM vitalitasaloon_ae_[dbo.CoreMessaging_Messages]
WHERE SenderUserID IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1) AND isDeleted = 1)
AND (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1);
DELETE FROM vitalitasaloon_ae_[dbo.CoreMessaging_MessageRecipients]
WHERE MessageID IN (SELECT MessageID FROM vitalitasaloon_ae_[dbo.CoreMessaging_Messages] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1))
AND UserID IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1) AND isDeleted = 1);
DELETE FROM vitalitasaloon_ae_[dbo.CoreMessaging_Subscriptions]
WHERE PortalID = IsNull(@PortalID, PortalID)
AND UserID IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1) AND isDeleted = 1);
-- delete journal:
DELETE FROM vitalitasaloon_ae_[dbo.Journal]
WHERE UserID IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1) AND isDeleted = 1)
AND PortalID = IsNull(@PortalID, PortalID);
DELETE FROM vitalitasaloon_ae_[dbo.Journal_Comments]
WHERE CommentId IN (SELECT CommentID FROM vitalitasaloon_ae_[dbo.Journal] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1))
AND UserID IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1) AND isDeleted = 1);
-- delete userfolders records:
DELETE FROM vitalitasaloon_ae_[dbo.Folders]
FROM vitalitasaloon_ae_[dbo.Folders] F
JOIN vitalitasaloon_ae_[dbo.UserPortals] U ON FolderPath LIKE 'Users/%/' + Cast(U.UserID As nVarChar) AND U.PortalID = IsNull(@PortalID, U.PortalID)
WHERE U.isDeleted = 1;
-- delete HtmlTextUsers
DELETE FROM vitalitasaloon_ae_[dbo.HtmlTextUsers]
WHERE TabID IN (SELECT TabID FROM vitalitasaloon_ae_[dbo.Tabs] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1))
AND UserID IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1) AND isDeleted = 1);
-- delete RelationShips:
DELETE FROM vitalitasaloon_ae_[dbo.Relationships]
WHERE UserID IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1) AND isDeleted = 1)
AND (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1);
DELETE FROM vitalitasaloon_ae_[dbo.UserRelationships]
WHERE (UserID IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1) AND isDeleted = 1)
OR RelatedUserID IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1) AND isDeleted = 1))
AND RelationshipID IN (SELECT RelationshipID FROM vitalitasaloon_ae_[dbo.Relationships] WHERE PortalID = IsNull(@PortalID, PortalID));
DELETE FROM vitalitasaloon_ae_[dbo.UserRelationshipPreferences]
WHERE UserID IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals] WHERE PortalID = IsNull(@PortalID, PortalID) AND isDeleted = 1)
AND RelationshipID IN (SELECT RelationshipID FROM vitalitasaloon_ae_[dbo.Relationships] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1));
-- delete UserRoles:
DELETE FROM vitalitasaloon_ae_[dbo.UserRoles]
WHERE UserID IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1) AND isDeleted = 1)
AND RoleID IN (SELECT RoleID FROM vitalitasaloon_ae_[dbo.Roles] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1));
-- delete Profile:
DELETE FROM vitalitasaloon_ae_[dbo.Profile]
WHERE UserID IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals] WHERE (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1) AND isDeleted = 1)
AND (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1);
-- delete UserPortals:
DELETE FROM vitalitasaloon_ae_[dbo.UserPortals]
WHERE isDeleted = 1
AND (PortalID = @PortalID OR IsNull(@PortalID, -1) = -1);
-- finally delete all user accounts, not associated with a website anymore (except superusers)
DELETE FROM vitalitasaloon_ae_[dbo.PasswordHistory]
WHERE UserID IN (SELECT UserID
FROM vitalitasaloon_ae_[dbo.Users] U
WHERE isSuperuser = 0
AND UserID NOT IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals]));
DELETE FROM vitalitasaloon_ae_[dbo.UserAuthentication]
WHERE UserID IN (SELECT UserID
FROM vitalitasaloon_ae_[dbo.Users] U
WHERE isSuperuser = 0
AND UserID NOT IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals]));
DELETE FROM vitalitasaloon_ae_[dbo.Users]
WHERE isSuperuser = 0
AND UserID NOT IN (SELECT UserID FROM vitalitasaloon_ae_[dbo.UserPortals]);
DELETE FROM dbo.aspnet_Membership
WHERE ApplicationID IN (SELECT ApplicationID FROM dbo.aspnet_Applications WHERE ApplicationName = 'DotNetNuke')
AND UserId IN (SELECT UserID FROM dbo.aspnet_Users WHERE UserName NOT IN (SELECT UserName FROM vitalitasaloon_ae_[dbo.Users]))
DELETE FROM dbo.aspnet_Users
WHERE ApplicationID IN (SELECT ApplicationID FROM dbo.aspnet_Applications WHERE ApplicationName = 'DotNetNuke')
AND UserName NOT IN (SELECT UserName FROM vitalitasaloon_ae_[dbo.Users])
END;
GO
Exec vitalitasaloon_ae_[dbo.RemoveAllDeletedUsers] -1;
GO
-----------------------------------------
ERROR
-----------------------------------------
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near 'dbo.RemoveAllDeletedUsers'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 2
Incorrect syntax near 'dbo.RemoveAllDeletedUsers'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 7
Incorrect syntax near 'dbo.DesktopModulePermission'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 13
Incorrect syntax near 'dbo.FolderPermission'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 19
Incorrect syntax near 'dbo.ModulePermission'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 25
Incorrect syntax near 'dbo.TabPermission'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 32
Incorrect syntax near 'dbo.UserProfile'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 39
Incorrect syntax near 'dbo.CoreMessaging_Messages'.
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 40
Must declare the scalar variable "@PortalID".
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 44
Incorrect syntax near 'dbo.CoreMessaging_MessageRecipients'.
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 45
Must declare the scalar variable "@PortalID".
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 46
Must declare the scalar variable "@PortalID".
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 48
Incorrect syntax near 'dbo.CoreMessaging_Subscriptions'.
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 50
Must declare the scalar variable "@PortalID".
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 53
Incorrect syntax near 'dbo.Journal'.
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 54
Must declare the scalar variable "@PortalID".
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 58
Incorrect syntax near 'dbo.Journal_Comments'.
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 59
Must declare the scalar variable "@PortalID".
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 60
Must declare the scalar variable "@PortalID".
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 63
Incorrect syntax near 'dbo.Folders'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 69
Incorrect syntax near 'dbo.HtmlTextUsers'.
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 70
Must declare the scalar variable "@PortalID".
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 71
Must declare the scalar variable "@PortalID".
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 74
Incorrect syntax near 'dbo.Relationships'.
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 75
Must declare the scalar variable "@PortalID".
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 78
Incorrect syntax near 'dbo.UserRelationships'.
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 79
Must declare the scalar variable "@PortalID".
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 80
Must declare the scalar variable "@PortalID".
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 81
Must declare the scalar variable "@PortalID".
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 83
Incorrect syntax near 'dbo.UserRelationshipPreferences'.
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 84
Must declare the scalar variable "@PortalID".
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 85
Must declare the scalar variable "@PortalID".
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 88
Incorrect syntax near 'dbo.UserRoles'.
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 89
Must declare the scalar variable "@PortalID".
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 90
Must declare the scalar variable "@PortalID".
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 93
Incorrect syntax near 'dbo.Profile'.
Msg 137, Level 15, State 2, Procedure vitalitasaloon_ae_, Line 94
Must declare the scalar variable "@PortalID".
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 98
Incorrect syntax near 'dbo.UserPortals'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 105
Incorrect syntax near 'dbo.PasswordHistory'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 107
Incorrect syntax near 'U'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 109
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 111
Incorrect syntax near 'dbo.UserAuthentication'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 113
Incorrect syntax near 'U'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 115
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Procedure vitalitasaloon_ae_, Line 117
Incorrect syntax near 'dbo.Users'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.