Solved

Delete All Trial Users in a database using Membership model

Posted on 2014-10-02
3
129 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 50

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

696 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