Removing defunct/missing applications' references from ASP.NET SQL memership DB?

Question

Creating my first site that uses the login/membership system provided in ASP.NET (v4).

So far all is good, used the web.config to point it at a remote SQL server, and it's working.

What I didn't do originally was specify an 'applicationName' for the membership provider, so when looking at the table(s) in the SQL server it shows the name as "/".

I updated the web.config and added the Membership and Provider sections needed to assign a name, assigned one, and ran the ASP.NET Configuration Wizard to re-initialize the user DB.

In the SQL server the application now shows its name and a new GUID, and works as expected.

My question is, is there an automated way to get rid of all the (now unneeded) records associated to the 'old site' from the various ASP.NET SQL table(s), or am I stuck doing it manually?

If I need to do this manually, is anyone aware of a laid out set of procedures to ensure all references are removed from the DB?

Answer 1

I have used this script on my development machine with no issues in the past. I can't say that I have ever really gone to town with the membership provider so I have only ever had data in the users and roles table but I think this script should tidy everything up.

Make a backup before you run it of course as I haven't added any transactions around it.

DECLARE @APPID UNIQUEIDENTIFIER 

--Change the application id to the app you want to clear out 
SET @APPID = 'bb5f1064-062d-4a21-875c-dc15c1e9ec27'

delete from aspnet_Membership Where UserId in ( Select UserId From aspnet_Users Where ApplicationId = @APPID)
delete from aspnet_PersonalizationPerUser Where PathId in ( Select PathId From aspnet_Paths Where ApplicationId = @APPID)
delete from aspnet_PersonalizationAllUsers Where PathId in ( Select PathId From aspnet_Paths Where ApplicationId = @APPID)
delete from aspnet_Paths Where ApplicationId = @APPID
delete from aspnet_PersonalizationPerUser Where UserId in ( Select UserId From aspnet_Users Where ApplicationId = @APPID)
delete from aspnet_Profile Where UserId in ( Select UserId From aspnet_Users Where ApplicationId = @APPID)
delete from aspnet_UsersInRoles Where RoleId in( Select RoleId from aspnet_Roles Where ApplicationId = @APPID)
delete from aspnet_Roles Where ApplicationId = @APPID
delete from aspnet_Users Where ApplicationId = @APPID
delete from aspnet_Applications Where ApplicationId = @APPID