Deleting Unauthorized Users in bulk through SQL

Overview

If you would like to purge all unauthorized users for your portal because there are hundreds of Unauthorized users that are waiting for approval, you can delete them in bulk through a SQL script.

 

Solution

Please note that since this process involves directly updating the database, it is highly recommended to create a backup first. The steps to bulk delete unauthorized users are as follows:

1. Log in as a superuser and navigate to the SQL Console on the website using the steps in our KB article Running SQL Queries using the SQL Console.

2. [Optional] To list all the non-deleted and unauthorized users using the SQL console, please run the below query. This query does not make any changes to the database, it just lists user accounts.

select A.UserID, A.Username, A.FirstName, A.LastName, A.Email, B.Authorised, B.IsDeleted
from
Users A join UserPortals B
on
A.UserID = B.UserID
where
B.Authorised = 0 and B.IsDeleted = 0

 

3. Running the below update query will mark all unauthorized user accounts as deleted.

Update UserPortals 
SET IsDeleted=1
where
Authorised = 0 and IsDeleted = 0

4. Go to Settings > Clear Cache to see the change reflected in the UI.

4. Once the unauthorized user accounts are marked as deleted, you can follow these steps Purge Deleted Users if desired.

Comments

0 comments

Please sign in to leave a comment.