?
Solved

Delete All Trial Users in a database using Membership model

Posted on 2014-10-02
3
Medium Priority
?
130 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 1000 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 51

Accepted Solution

by:
Vitor Montalvão earned 1000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

752 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