Delete All Trial Users in a database using Membership model

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
RBSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RBSAuthor Commented:
Hi:

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

Regards,
RBS
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.