Recently I came into a problem when I accidentally added a user to CRM that caused the entire CRM instance to no longer be usable. What happened is the user shared I added to an instance was the user that created the CRM instance. That cause the whole instance to throw an error any time anyone would load it up.
Now the errors I was receiving were: “SecLib::RetrievePrivilegeForUser failed – no roles are assigned to user. Returned hr = -2147209463, User: [userid]”
“Microsoft.Crm.BusinessEntities.CrmObjectNotFoundException: systemuser With Id = [userid] Does Not Exist at Microsoft.Crm.BusinessEntities.BusinessProcessObject.Retrieve(“
I managed to find a Microsoft Support article on the CRM Crash with the cause and it matched up with actions that caused the crash but it only offers pre-emptive actions to prevent the issue occurring and not what to do when it occurs.
Since I couldn’t go directly into the instance to remove the user it became very difficult resolve this issue. I am NOT a very heavy user of CRM and I wasn’t exactly sure of all the tables that would have an affect on the user. So I went into database and created this script to find every table that had a User ID column.
SELECT s.name + '.' + o.name FROM sys.objects O INNER JOIN sys.columns C ON C.object_id = O.object_id INNER JOIN sys.schemas S ON S.schema_id = O.schema_id WHERE C.name like '%UserId%' AND O.type = 'U'
Then I’m created another script that would take a list of all those tables and create a statement that would through and delete all the records that reference that User ID. This ended up working great but I had to run the script a couple of times to remove all the foreign key references. I also have to go into the main CRM database and run it off of that that as well to remove the main user record and associations.
SELECT 'DELETE FROM ' + s.name + '.' + O.name + ' WHERE ' + C.Name + ' = ''00000000-0000-0000-0000-000000000000''' FROM sys.objects O INNER JOIN sys.columns C ON C.object_id = O.object_id INNER JOIN sys.schemas S ON S.schema_id = O.schema_id WHERE C.name like '%UserId%' AND C.Name NOT LIKE '%@%' AND O.type = 'U'
As with any time you work directly with the database it is important to backup everything before you do any major deletions. This should also be used with caution and only as a last ditch effort when you can’t get into the system and you know exactly what is causing the problem. In my case it worked great to remove a user completely and immediately after the system started failing. Of course the better option in this would have to not tried to add the user who was running the CRM App Pool to CRM itself.