Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 133
  • Last Modified:

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
0
RBS
Asked:
RBS
2 Solutions
 
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
 
RBSAuthor Commented:
Hi:

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

Regards,
RBS
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now