Organization Units Migration
When upgrading your existing Orchestrator where multiple Organization Units were defined to v2019.10, certain conditions could result in the existing OU's not being properly migrated to the Folders paradigm. Specifically, this occurs when an OU with one or more assigned users is deleted and results in those users being unable to use Orchestrator.
Two scripts are provided to correct this issue if it occurs in your Orchestrator upgrade:
Used in conjunction, this will delete any affected User to Organization Unit assignments for deleted OU's:
- Download both scripts from the links above.
- Run SelectUndeletedUserOrganizationEntires.sql and save the resulting data.
- Run DeleteAffectedUserOrganizationUnitsEntries.sql.
Concurrent Folder Create Requests
Users upgrading from Orchestrator versions prior to v2019.10 are impacted by an issue occurring due to concurrent folder create requests. The problem causes some user roles to be displayed incorrectly while also affecting the process of deleting or reassigning users.
If you encounter this problem, run the following script:
DECLARE @duplicates TABLE([Id] BIGINT, [Code] NVARCHAR(95), [TenantId] INT)
INSERT INTO @duplicates
SELECT ou.[Id], ou.[Code], ou.[TenantId]
FROM [dbo].[OrganizationUnits] ou
INNER JOIN
(SELECT duplicates.[Code]
FROM [dbo].[OrganizationUnits] duplicates
WHERE duplicates.[IsDeleted] = 0 AND duplicates.[ProvisionType] = 0
GROUP BY duplicates.[Code]
HAVING COUNT(duplicates.[Code]) > 1) a ON a.[Code] = ou.[Code]
WHERE ou.[IsDeleted] = 0 AND ou.[ProvisionType] = 0
DECLARE @id BIGINT
DECLARE @code NVARCHAR(95)
DECLARE @tenantId INT
DECLARE @rootCode NVARCHAR(95) = (SELECT TOP 1 [Code] FROM [dbo].[OrganizationUnits])
DECLARE @rootCodeLength INT = LEN(IIF(CHARINDEX('.', @rootCode) > 0, SUBSTRING(@rootCode, 0, CHARINDEX('.', @rootCode)), @rootCode))
DECLARE duplicates_Cursor CURSOR FOR
SELECT * FROM @duplicates
OPEN duplicates_Cursor
FETCH NEXT FROM duplicates_Cursor INTO @id, @code, @tenantId
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
DECLARE @result int;
DECLARE @resource NVARCHAR(30) = 'Tenant#' + CAST(@tenantId AS NVARCHAR(10)) + '.RootFolder#0'
EXEC @result = sp_getapplock @resource, 'Exclusive', 'Transaction', 10000;
DECLARE @lastCode NVARCHAR(95) = (SELECT MAX([Code]) FROM [dbo].[OrganizationUnits] WHERE [IsDeleted] = 0)
DECLARE @nextCode NVARCHAR(95) = CAST(CAST(@lastCode AS BIGINT) + 1 AS NVARCHAR(95))
UPDATE [dbo].[OrganizationUnits]
SET [Code] = REPLICATE('0', @rootCodeLength - LEN(@nextCode)) + @nextCode
WHERE [Id] = @id
FETCH NEXT FROM duplicates_Cursor INTO @id, @code, @tenantId
COMMIT TRAN;
END
CLOSE duplicates_Cursor
DEALLOCATE duplicates_Cursor
Updated about a year ago