0121 31 45 374
Qoute Icon

How to: Remove all users from the ASP.Net Membership database by application name

Tim

delete_key[1]A while ago I wrote about How to: Remove users from the ASP.Net membership database which showed you how to remove a single user from the ASP.Net membership database from SQL Management Studio. That's great when you're deleting one or two users but what if you're testing and need to delete all users associated with an application?

There's a little more work involved with that one but it's not too difficult, rather than passing in the member's id, you pass in the ApplicationName (same as you set in your web.config) and it will find the various users that match and remove them for you.

USE DatabaseName --This should be your database name
GO

BEGIN TRANSACTION

DECLARE @ApplicationName nvarchar(256)
SET @ApplicationName = '##YOUR APPLICATION NAME -AS SET IN THE WEB.CONFIG ##'

--Lowercase it so it matches LoweredApplicationName exactly 
SET @ApplicationName = LOWER(@ApplicationName)

--Values in the aspnet_Profile table
DELETE p
FROM
	dbo.aspnet_Applications a 
		INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
		INNER JOIN dbo.aspnet_Profile p ON u.UserId = p.UserId
WHERE a.LoweredApplicationName = @ApplicationName

--Values in the aspnet_UsersInRoles table
DELETE r
FROM
	dbo.aspnet_Applications a 
		INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
		INNER JOIN dbo.aspnet_UsersInRoles r ON u.UserId = r.UserId
WHERE a.LoweredApplicationName = @ApplicationName

--Values in the aspnet_PersonalizationPerUser table
DELETE p
FROM
	dbo.aspnet_Applications a 
		INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
		INNER JOIN dbo.aspnet_PersonalizationPerUser p ON u.UserId = p.UserId
WHERE a.LoweredApplicationName = @ApplicationName

--Values in the aspnet_Membership table
DELETE m
FROM
	dbo.aspnet_Applications a 
		INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
		INNER JOIN dbo.aspnet_Membership m ON u.UserId = m.UserId
WHERE a.LoweredApplicationName = @ApplicationName

--Values in the aspnet_users table
DELETE u
FROM dbo.aspnet_Applications a INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
WHERE a.LoweredApplicationName = @ApplicationName

ROLLBACK TRANSACTION
< Back to Blog

Liked this post? Got a suggestion? Leave a comment