Reassigning records in Microsoft Dynamics CRM can be useful, especially when a user of an organization decides to leave. What is not useful is when the process built into CRM fails to do so. This process can fail for a number of reasons including a process timing out, the process getting stuck on the “Reassigning records…” dialog, or third party code preventing such a thing from happening (if you own your product, this is actually a known bug depending on what version you are on). Rest assured – there is a simple solution to this, even though it’s not “supported” by Microsoft (it hasn’t caused any problems for current customers that need us to do this for them). This solution will only work for people who are using an On-Premise version of CRM and have direct SQL access.
This script loops through every table in SQL that has a column named ‘ownerid’ and updates it accordingly. The ‘ownerid’ column is a column that is created by CRM, so we can uniformly assume that it will have the same name in every table. This is not the case with, say, a contact lookup because there exists contact look ups in CRM that are created by CRM, and then there are contact look ups created by other people with a prefixed attached. Not only that but CRM does not prevent a user from creating a look up field to a contact with a schema name of “new_accountid”. If you wish to bulk update other columns, I would not suggest using this script unless you know exactly what you are doing and know SQL to modify it.
To run this script, open SQL Management Studio and connect to the database that is hosting your CRM Organization. Once connected, in the top left drop down, choose the database that corresponds to your CRM organization (you can visit http://ServerName:Port/OrgName/home/home_debug.aspx replacing Server Name, Port and Organization Name to reflect your installation to verify which database is the correct one).
Once you have done that, click new query and copy and paste the below script into the window. Now, navigate to the two user records in CRM to get the unique identifier this script needs to run (how to get the GUID of a record https://social.msdn.microsoft.com/Forums/en-US/72746ce0-9aee-44d8-9bf6-6db80e933888/unique-id-to-accounts-and-contacts-in-crm2011?forum=crm). A uniform way to get this identifier from CRM, regardless of version is to click the email link button in CRM. This will open up an email dialog box with a URL in the body of the message. That URL contains the 36-character identifier that we need which will occur in-between “…&id=%7b*id here*%7d…”. Copy that in as the appropriate toId or fromId value in the script, leaving the single quote wrapped around it. After that is complete, hit the “! Execute” button, which should take a matter of seconds to execute. Once that has completed, verify in CRM that the records were reassigned. Cheers!
Declare @fromId uniqueidentifier
Set @fromId = '967098BB-9EC7-E111-AF13-BC305B2A71B1'
Declare @toId uniqueidentifier
Set @toId = '2DC2DFEA-4C52-E511-B9F0-00155DA16D0C'
Declare @columnName nvarchar(100)
Set @columnName = 'ownerid'
DECLARE @TableName nvarchar(300)
Declare @sqlStatement nvarchar(500)
DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
inner join INFORMATION_SCHEMA.Tables
on INFORMATION_SCHEMA.Tables.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE INFORMATION_SCHEMA.Tables.TABLE_TYPE = 'BASE TABLE' and COLUMN_NAME = @columnName
and INFORMATION_SCHEMA.Tables.TABLE_NAME not in ('UserEntityUISettingsBase', 'OwnerBase')
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
print @TableName
set @sqlStatement =
'update ' + @TableName +
' set ' + @columnName + ' = ''' + cast(@toId as nvarchar(36)) + '''' +
' from ' + @TableName +
' where ' + @columnName + ' = ''' + cast(@fromId as nvarchar(36)) + ''''
exec (@sqlStatement)
FETCH NEXT FROM MY_CURSOR INTO @TableName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
Again, please remember that, according to Microsoft, this is an unsupported customization, so use this at your own risk and please make sure to back up your CRM database before running this script so you can recover it if something goes wrong.