Deleting SQL users in bulk

ZZ
ZZ
Vote:
 

Hi,

Is it possbile to delete SQL users (in bulk) in EPiServer. I can delete one by one but we have a lot of inactive users which I want to delete in bulk.

May be some sql query ? 

#267920
Dec 06, 2021 12:17
Vote:
 

You could do it in a SQL query for sure but if it's an ongoing problem and you want to delete regularlly inactive users I'd suggest just build a scheduled job and doing it via code.

#267923
Dec 06, 2021 14:35
ZZ
Vote:
 

Thanks for your reply

Do you have any query to do it ?

It will not be an ongoing problem as we are moving to AD and no user would be create directly in EPiServer DB.

#267924
Dec 06, 2021 14:42
Vote:
 

It's just SQL Membership, this is the structure to delete a single user by the unique identifer of the user. So just write a SQL script to pull the users out based on whatever criteria you want and run this for each

DECLARE @UserId uniqueidentifier
SET @UserId = 'GUID_for_UserId'
DELETE FROM aspnet_Profile WHERE UserID = @UserId
DELETE FROM aspnet_UsersInRoles WHERE UserID = @UserId
DELETE FROM aspnet_PersonalizationPerUser WHERE UserID = @UserId
DELETE FROM dbo.aspnet_Membership WHERE UserID = @UserId
DELETE FROM aspnet_users WHERE UserID = @UserId
#267925
Dec 06, 2021 14:47
ZZ
Vote:
 

Thanks a lot

#267926
Dec 06, 2021 14:48
Vote:
 

If you want a DELETE with a WHERE clause: this is standard SQL.

What you can do is batch deletes like this:

SELECT 'Starting' --sets @@ROWCOUNT WHILE @@ROWCOUNT <> 0 DELETE TOP (xxx) MyTable WHERE ...

Or if you want to remove a very high percentage of rows...

SELECT col1, col2, ... INTO #Holdingtable
           FROM MyTable WHERE ..opposite condition.
TRUNCATE TABLE MyTable
INSERT MyTable (col1, col2, ...)
           SELECT col1, col2, ... FROM #Holdingtable

I

#268043
Dec 08, 2021 13:08
ZZ
Vote:
 

Is there any way to do it via UI / admin site ?  

#268196
Dec 10, 2021 15:36
Scott Reed - Dec 10, 2021 15:37
not that I'm aware of
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.