Removing Pending Users from system

[avillanueva]

Doing a bit of house keeping. When I pull active user report, I get 6 users back that are pending from what I can tell in the WTUser table. They are not in my LDAP and were created over 10 years ago. I am looking to clear them out. I check all current and delete projects and I cannot see them. I do not see them part of any membership. The article below says you can link them by creating a user with the same email address. this may only be possible for 1 of them since the others have email addresses like "g" and "jr". It looks like there was a parsing error and their name got split in many users.

https://www.ptc.com/en/support/article/CS38011?source=search

I believe this was related to an old project where you were able to invite users by email. These always show up in my Active User report. Should I just flip the DISABLED bit to 1 and call it a day?

[HelesicPetr]

Hi @avillanueva 

If the users are not in ldap and are not in any team or group and were accidentally created I would delete them. 

PetrtH

[avillanueva]

But how? They cannot be deleted from UI since they do not return in searches. I could delete from table but if I missed a reference, that could throw an error. Safer to mark disabled?

[TomU]

They don't appear when searching for disconnected participants???  (From the UI)

[avillanueva]

Nope, I think cause they are pending users. Docs state to create a user with same email and it will link up to it. I think I will just disable in DB.

[HelesicPetr]

@avillanueva 

aha , I would check database user table and try if the row can be deleted from DB without any consequence problem.

PetrH. 

[mmeadows-3 · Accepted solution]

You are correct, pending users are user accounts that were invited to join a project back before Windchill 10.0, but never did.  So, there should be an entry in the WTUser table.  I think the Status column says "Pending" but am not certain.  As I recall, the other participant related tables (RemoteObjectInfo, RemoteObjectId, and OwningRepositoryLocalObject) weren't populated until the user logs in for the first time.  See CS109367 for a map of the table relationships.

Also, there shouldn't be any associations (workspaces, user preferences, saved searches, etc.) because they never logged in.  Please confirm the user tables aren’t populated in these other tables.  These commands are written for SQL Server.

-- User Information

select idA2A2, name, disabled, repairNeeded from WTUser where name='John.Doe';

-- Should return: #######, John.Doe, 0, 0

-- Does the user have an entry in the RemoteObjectId table?

select roid.idA2A2,roid.remoteObjectId from RemoteObjectId roid, RemoteObjectInfo roin, WTUser wtu where wtu.idA2A2=roin.idA3A3 and roin.remoteId=roid.idA2A2 and wtu.idA2A2='#######';

-- Does the user have an entry in the RemoteObjectInfo table?

select roin.idA3A3,roin.birthId,roin.lastKnownId,roin.remoteId from RemoteObjectInfo roin, WTUser wtu where wtu.idA2A2=roin.idA3A3 and wtu.idA2A2='#######';

-- Does the user have an entry in the OwningRepositoryLocalObject table?

select idA3B5,idA3A5 from OwningRepositoryLocalObject where idA3B5=(select idA2A2 from WTUser where idA2A2='#######');

Cleanup depends on corporate policy for handling users who don't use Windchill.  My preference for users who never accessed Windchill is to just delete them.  Since these don't show up via the Participants Administration GUI, you can delete them directly from the database.

Disclaimer: PTC does not recommend hacking the database with good reason.  You can really screw things up if you aren't careful.

BUT... sometimes the GUI breaks because the user accounts are screwed up or just doesn’t display users that are deleted or pending.  These commands should work in SQL Server.

Backup tables

First, backup these tables if you aren't 100% confident or are playing in production.

Oracle:

create table WTUser_bak as select * from WTUser;

create table OwningRepLocalObject_bak as select * from OwningRepositoryLocalObject;

create table RemoteObjectInfo_bak as select * from RemoteObjectInfo;

create table RemoteObjectId_bak as select * from RemoteObjectId;

SQL Server:

select * into WTUser_bak from WTUser;

select * into OwningRepLocalObject_bak from OwningRepositoryLocalObject;

select * into RemoteObjectInfo_bak from RemoteObjectInfo;

select * into RemoteObjectId_bak from RemoteObjectId;

Then delete the user information from the participant tables in this order and cleanup the WTUser table entry.  These queries assume I was wrong about the tables getting populated.

-- User Information

select idA2A2, name, disabled, repairNeeded from WTUser where name='John.Doe';

-- Should return: #######, John.Doe, 0, 0

-- Remove from RemoteObjectId

delete from RemoteObjectId where idA2A2=(select roin.remoteId from RemoteObjectInfo roin, WTUser wtu where wtu.idA2A2=roin.idA3A3 and wtu.idA2A2='#######');

-- Remove from RemoteObjectInfo

delete from RemoteObjectInfo where idA3A3=(select idA2A2 from WTUser where idA2A2='#######');

-- Remove from OwningRepositoryLocalObject

delete from OwningRepositoryLocalObject where idA3B5=(select idA2A2 from WTUser where idA2A2='#######');

-- Update in WTUser

update WTUser set disabled='1', repairNeeded='0', name='{' + classnameA2A2 + ':' + Cast(idA2A2 AS VARCHAR(10)) + '}' + name where idA2A2='#######';

When a user account deleted this way, we can restore it via command line (CS200852).

Hope this helps.