Group Membership (sys_user_grmember) cleanup job advice

[jMarshal]

I've noticed how we can get duplicate records in sys_user_grmember (same user/group combo existing on multiple records on this table). This happens because of automated processes that add members to groups without checking membership first, etc...but it happens and I want to manage this data better.

I was thinking of running a nightly script job or a flow which would delete these duplicate records, keeping the most recent one in all cases.

I do not need help with the script or flow, I got that covered...but I do want advice on my theory/thought process, if anyone has any.

I cannot imagine any negative down-stream effects which would come from deleting these duplicates...but I don't know of any oob platform features which may rely on this capability (having a user essentially be a member of a group "more than once"), that we will not be able to leverage in the future, which relies on this configuration choice....and I call it a "configuration choice" because it seems to me like it would otherwise be a good idea to include this type of maintenance oob without me needing to discover this and correct it...

...thoughts?

[r2024]

na

[jMarshal]

...or even better, perhaps an on-after insert BR on the sys_user_grmember table which deletes the previous record, if one exists...

...I just kinda find it hard to believe that the solution could be this simple and it hasn't been implemented as a platform feature yet...the only explanation would suggest intent and I don't wanna "cut off my nose despite my face" just because we sometimes see duplicate users/records in lists/collections/etc...

[Hemanth M1]

Hi @jMarshal ,

Yeah, automated way of doing this bypasses the system check (manual duplicate entries are not allowed).
I don't see any issues with cleaning up because we are not referencing any logic to this, all would inherit either from group or roles. In this - group membership and roles stay intact since you’re deleting a duplicate entry.

Additionally, please add this check for the automation job to avoid this!

[Rampriya-S · Accepted solution]

Hi @jMarshal, I’d recommend de-duplicating the table first and then creating a unique database index on the user and group columns. That will prevent duplicate memberships from being created at the database level going forward and eliminate the need for ongoing cleanup.