After testing DNN 5 I notice traces of user in the database after they are deleted from the "User Accounts" under admin options. If DNN is setup to use unique email address, after a user is delete and recreated with the same email address DNN returns "ERROR IN DATABASE EMAIL ADDRESS EXISTS". I created the following stored procedure to completely cleanse all traces of a user from the DNN database.
Please use the following STORED PROCEDURE at your own risk. I take no responsibility for misuse or damage this procedure could cause to your database. I was very careful with a ROLL BACK but mistakes do happen I suggest running this in test environment first.
-- BEGIN COPY CODE HERE
/****** Object: StoredProcedure [dbo].[Util_CleanseRemoveDNNuser] Script Date: 09/02/2009 05:53:51 ******/
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[Util_CleanseRemoveDNNuser]')
AND TYPE IN (N'P',N'PC'))
DROP procedure [dbo].[util_cleanseremovednnuser]
GO
/****** Object: StoredProcedure [dbo].[Util_CleanseRemoveDNNuser] Script Date: 09/02/2009 05:53:51 ******/
SET ansi_nulls ON
GO
SET quoted_identifier ON
GO
-- ==========================================================================================
-- Author: Matthew David Elgert © 2009 VESSEA
-- Create date: 9/2/2009
-- Description: SP Util_CleanseRemoveDNNuser (Cleanse username from the database)
-- Recommended to delete the user from DotNetNuke user interface then run this script.
-- Example usage: EXEC [dbo].[Util_CleanseRemoveDNNuser] 'YourUserName'
-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND
-- CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES,
-- INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
-- AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
-- COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
-- SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
-- PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
-- BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
-- CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
-- ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
-- DAMAGE.
-- DotNetNuke®, DNN®, and the DotNetNuke logo are trademarks of DotNetNuke Corporation
-- ==========================================================================================
CREATE PROCEDURE [dbo].[util_cleanseremovednnuser]
@UserName NVARCHAR(100)
AS
DECLARE @DNNUserID INT,
@ASPUserID UNIQUEIDENTIFIER
SET @DNNUserID = NULL
SET @ASPUserID = NULL
SELECT @DNNUserID = [userid]
FROM [dbo].[users]
WHERE [username] = @UserName
SELECT @ASPUserID = [userid]
FROM [dbo].[aspnet_users]
WHERE [username] = @UserName
BEGIN TRAN
PRINT 'Deleting DNNUserID '
+ CAST(@DNNUserID AS NVARCHAR(150))
PRINT 'Deleting ASP USerID '
+ CAST(@ASPUserID AS NVARCHAR(150))
PRINT 'Deleting UserName '
+ CAST(@UserName AS NVARCHAR(150))
DELETE FROM [dbo].[users]
WHERE [userid] = @DNNUserID
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
PRINT 'Rollback...'
RETURN
END
DELETE FROM [dbo].[userprofile]
WHERE [userid] = @DNNUserID
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
PRINT 'Rollback...'
RETURN
END
DELETE FROM [dbo].[aspnet_membership]
WHERE [userid] = @ASPUserID
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
PRINT 'Rollback...'
RETURN
END
DELETE [dbo].[aspnet_users]
WHERE [userid] = @ASPUserID
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
PRINT 'Rollback...'
RETURN
END
COMMIT TRAN
PRINT 'DNN USERID '
+ CAST(@UserName AS NVARCHAR(150))
+ ' deleted. From Table [dbo].[users]'
PRINT 'DNN USERID '
+ CAST(@UserName AS NVARCHAR(150))
+ ' deleted. From Table [dbo].[userprofile]'
PRINT 'DNN USERID '
+ CAST(@UserName AS NVARCHAR(150))
+ ' deleted. From Table [dbo].[aspnet_users]'
PRINT 'DNN USERID '
+ CAST(@UserName AS NVARCHAR(150))
+ ' deleted. From Table [dbo].[aspnet_membership]'
PRINT 'COMMIT TRAN...'
GO
-- END COPY CODE HERE