Solved

Delete All Trial Users in a database using Membership model

Posted on 2014-10-02
3
127 Views
Last Modified: 2014-10-06
Hi:

I'm hoping to develop a query that will delete all users and records in other related tables which are in the role of trial.

The following query gets all my trial users:

SELECT        u.UserName, ur.RoleId, r.RoleName, p.UserId
FROM            aspnet_Users u INNER JOIN
                         aspnet_UsersInRoles ur ON u.UserId = ur.UserId AND u.UserId = ur.UserId INNER JOIN
                         aspnet_Roles r ON ur.RoleId = r.RoleId AND ur.RoleId = r.RoleId INNER JOIN
                         aspnet_Membership p ON u.UserId = p.UserId AND u.UserId = p.UserId
WHERE        (r.RoleName = N'Trial')

Open in new window


I need to delete the relevant records in aspnet_Users, aspnet_UsersInRoles and aspnet_Membership tables.  Any help in figuring out how to do this greatly appreciated.  

RBS
0
Comment
Question by:RBS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 250 total points
ID: 40358494
These type of problems are easily with correct table relationships. If you have foreign keys with ON CASCADE DELETE option you can just delete the 'Trial' role and the records will be deleted from related tables automatically. If you do not have the foreign keys you need to execute four queries:
-- membership
BEGIN TRANSACTION
-- first test
SELECT        u.UserName, ur.RoleId, r.RoleName, p.UserId
FROM            aspnet_Users u INNER JOIN
                         aspnet_UsersInRoles ur ON u.UserId = ur.UserId AND u.UserId = ur.UserId INNER JOIN
                         aspnet_Roles r ON ur.RoleId = r.RoleId AND ur.RoleId = r.RoleId INNER JOIN
                         aspnet_Membership p ON u.UserId = p.UserId AND u.UserId = p.UserId
WHERE        (r.RoleName = N'Trial')

DELETE p
FROM            aspnet_Users u INNER JOIN
                         aspnet_UsersInRoles ur ON u.UserId = ur.UserId AND u.UserId = ur.UserId INNER JOIN
                         aspnet_Roles r ON ur.RoleId = r.RoleId AND ur.RoleId = r.RoleId INNER JOIN
                         aspnet_Membership p ON u.UserId = p.UserId AND u.UserId = p.UserId
WHERE        (r.RoleName = N'Trial')

-- users                                  
DELETE u
FROM            aspnet_Users u INNER JOIN
                         aspnet_UsersInRoles ur ON u.UserId = ur.UserId AND u.UserId = ur.UserId INNER JOIN
                         aspnet_Roles r ON ur.RoleId = r.RoleId AND ur.RoleId = r.RoleId 
WHERE        (r.RoleName = N'Trial')

-- users in roles
DELETE ur
FROM              aspnet_UsersInRoles ur INNER JOIN
                         aspnet_Roles r ON ur.RoleId = r.RoleId AND ur.RoleId = r.RoleId INNER JOIN
                         aspnet_Membership p ON u.UserId = p.UserId AND u.UserId = p.UserId
WHERE        (r.RoleName = N'Trial')
                                  
-- role
DELETE 
FROM              aspnet_Roles 
WHERE        (RoleName = N'Trial')

-- check
SELECT        u.UserName, ur.RoleId, r.RoleName, p.UserId
FROM            aspnet_Users u INNER JOIN
                         aspnet_UsersInRoles ur ON u.UserId = ur.UserId AND u.UserId = ur.UserId INNER JOIN
                         aspnet_Roles r ON ur.RoleId = r.RoleId AND ur.RoleId = r.RoleId INNER JOIN
                         aspnet_Membership p ON u.UserId = p.UserId AND u.UserId = p.UserId
WHERE        (r.RoleName = N'Trial')

-- perform some other checks to make sure that you have not deleted too much
-- Now: either commit or rollback
--ROLLBACK
--COMMIT

Open in new window

Disclaimer: Please make a backup before doing that
0
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40358853
You need 3 DELETE statements. One by table:
DELETE FROM aspnet_Membership
WHERE EXISTS (SELECT 1 
			FROM aspnet_UsersInRoles ur 
				INNER JOIN aspnet_Roles r ON ur.RoleId = r.RoleId 
			WHERE r.RoleName = N'Trial' AND ur.UserId = aspnet_Membership.UserId)


DELETE FROM aspnet_UsersInRoles
WHERE EXISTS (SELECT 1 
			FROM aspnet_Roles r 
			WHERE r.RoleName = N'Trial' AND r.RoleId = aspnet_UsersInRoles.RoleId)


DELETE FROM aspnet_Users
WHERE NOT EXISTS (SELECT 1 
				FROM aspnet_UsersInRoles ur 
				WHERE r.RoleName = N'Trial' AND ur.UserId = aspnet_Users.UserId)

Open in new window

0
 

Author Closing Comment

by:RBS
ID: 40365014
Hi:

Thanks for this - both solutions were useful to me in figuring out how to do this.  

Regards,
RBS
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question