I recently had an issue with a user having issues logging in to certain applications.
The user could log in to some apps but recieved an error on others – for instance profiles, activities, communities and forums the user was fine.
Each applications database holds information regarding the user login and their external / directory / guid id – this guid is unique to the user – I found after extensive investigation that this user had orphaned entries in the other application DB tables.
The SQL queries and statements used to resolve this were *fun* to work out and they may be slightly different for each user that has this issue, but it should be fairly straight forward to work out once you know what the issue is.
*NOTE* – below is the solution that I used to resolve the issue – it will be / may be different for each user with a similar problem. It is advised where possible to test this on a back up of the DB to ensure it resolves the issue. Always back the databases up before making any change.
Firstly look up the user in the profiles database and gather thier login id (prof_uid) and external directory id (prof_guid)
Once you have these you are ready to start the investigation.
In the case of this user the SQL was as follows :
CORRECT GUID / EXT / DIRECTORY ID = C7B75D042B4C7C7B8025791100311ADA
== BLOGS ==
select * from blogs.rolleruser where username =’jsmith’;
select * from blogs.rollerloginname where username like ‘jsmit%’;
get the user id = 62537efa-3959-42a4-84f3-5e1fdc8cfac0
select * from blogs.rollerloginname where userid = ‘62537efa-3959-42a4-84f3-5e1fdc8cfac0’;
delete from blogs.rollerloginname where userid = ‘62537efa-3959-42a4-84f3-5e1fdc8cfac0′;
delete from blogs.rolleruser where username =’jsmith’;
== DOGEAR ==
select * from dogear.personlogin where loginname like ‘jsmit%’;
get the person_id = 436b98eb-59a9-420f-90d6-22b7a4926e00
select * from dogear.personlogin where person_id = ‘436b98eb-59a9-420f-90d6-22b7a4926e00’;
delete from dogear.personlogin where person_id = ‘436b98eb-59a9-420f-90d6-22b7a4926e00′;
select * from dogear.person where person_id=’436b98eb-59a9-420f-90d6-22b7a4926e00′;
delete from dogear.person where person_id=’436b98eb-59a9-420f-90d6-22b7a4926e00’;
== FILES ==
SELECT * FROM FILES.USER_TO_LOGIN where login_id = ‘jsmith’;
delete FROM FILES.USER_TO_LOGIN where login_id = ‘jsmith’;
select * from FILES.LIBRARY where title like ‘John Smit%’;
get label – C53524EEDB0F84E8802578C5002676AD
delete from FILES.LIBRARY where label =’C53524EEDB0F84E8802578C5002676AD’;
SELECT * FROM FILES.”USER” where name = ‘John Smith’;
delete FROM FILES.”USER” where name = ‘John Smith’;
== forums not an issue – has the correct GUID ==
select * from forum.df_memberlogin where loginname like ‘jsmit%’;
get memberid = d1140454-09ac-4484-a50e-ce914e573e7d
== HOMEPAGE ==
select * from homepage.loginname where loginname like ‘jsmit%’;
get person_id = cf48e29d-7d89-4f8a-acf0-47b9a8bcb98a
select * from homepage.loginname where person_id = ‘cf48e29d-7d89-4f8a-acf0-47b9a8bcb98a’;
delete from homepage.loginname where person_id = ‘cf48e29d-7d89-4f8a-acf0-47b9a8bcb98a’;
select * from homepage.person where displayname = ‘John Smith’;
get person_id of the incorrect GUID – db306bce-40cc-413a-b93c-1ad61a24cdae
select * from homepage.hp_ui where person_id in (‘cf48e29d-7d89-4f8a-acf0-47b9a8bcb98a’,’db306bce-40cc-413a-b93c-1ad61a24cdae’);
make note of any person IDs that bring back any entries – cf48e29d-7d89-4f8a-acf0-47b9a8bcb98a
and ui_ids – 45cbd2dc-aefa-46f4-9607-654ddab953d8
select * from homepage.hp_tab_inst where ui_id like ‘%45cbd2dc-aefa-46f4-9607-654ddab953d8’;
make note of full ui_id – 45cbd2dc-aefa-46f4-9607-654ddab953d8
make a note of tab_inst_id b1072db9-c553-4a04-8366-e7d26a415edb b9114d19-4d97-42b0-8760-580cc956abe8
select * from HOMEPAGE.HP_WIDGET_INST where tab_inst_id in (‘b1072db9-c553-4a04-8366-e7d26a415edb’,’b9114d19-4d97-42b0-8760-50cc956abe8′);
delete from HOMEPAGE.HP_WIDGET_INST where tab_inst_id in (‘b1072db9-c553-4a04-8366-e7d26a415edb’,’b9114d19-4d97-42b0-8760-580cc956abe8′);
delete from homepage.hp_tab_inst where ui_id = ’45cbd2dc-aefa-46f4-9607-654ddab953d8′;
delete from homepage.hp_ui where person_id in (‘cf48e29d-7d89-4f8a-acf0-47b9a8bcb98a’);
delete from homepage.person where person_id in (‘db306bce-40cc-413a-b93c-1ad61a24cdae’);
correct person id = e8238bbd-255f-4609-8a54-e28128f3e66b
== Activities is ok – is the correct GUID==
select * from activities.oa_memberlogin where loginname like ‘jsmit%’;
get memberid – CACG7F00000152B3E7EBA823194CED0000C6
SELECT * FROM ACTIVITIES.OA_MEMBERPROFILE where memberid=’CACG7F00000152B3E7EBA823194CED0000C6′;
== Communities is ok has the correct GUID ==
select * from sncomm.memberlogin where loginname like ‘jsmit%’;
get member_uuid – 0e56702f-9f37-4f2c-b295-2dd3250da726
select * from sncomm.memberprofile where display = ‘John Smith’;
== Wikis ==
SELECT * FROM WIKIS.”USER” where name like ‘John%’;
select * from wikis.user_to_login where login_id like ‘jsmit%’;
delete from wikis.user_to_login where login_id = ‘jsmith’;
delete from wikis.user_to_login where login_id = ‘jsmith@org.com’;
==
When these statements had been run the user can log in correctly as the additional orphaned entries have been removed.
Please note that due to the data and the contstraints on the database that there may be additional statements required – the SQL above is a guide on how I resolved the issue.
It was a FUN FUN FUN one to sort out .. I do love a good problem:)