How to: Remove all users from the ASP.Net Membership database by application name
Wednesday, December 22, 2010 7:08:47 PM (GMT Standard Time, UTC+00:00)
![delete_key[1]](http://blogs.thesitedoctor.co.uk/tim/images/53bad6f4d949_DE2B/delete_key1.jpg "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
Talk about a confusing error message
Wednesday, June 18, 2008 11:37:58 PM (GMT Daylight Time, UTC+01:00)
I don't mind when I get told I've made a mistake -or there's a problem with the system but this error message kinda takes the P! Quite what the developers were thinking when they wrote this one I'm not sure!
What do I do? celebrate that it went through ok or commiserate because it failed?
The "Ok." relates to the transaction completing without an issue, the "Stop" actually says that it failed so it's not even "Part A was ok, but Part B failed". Really odd, someone needs to look into testing their system.
Looks pretty though!