1. Home
  2. Archive Shuttle
  3. How to make archives assigned to leavers ownerless

How to make archives assigned to leavers ownerless

Overview

Archive Shuttle does not update a user record and owner assignment after initial AD sync.  Therefore a deletion of a record from AD when a user leaves (resp. has no mailbox associated anymore) is not reflected in Archive Shuttle database after next AD sync. Such archive, that is in fact ownerless, cannot be mapped with Leavers functionality, as it seems to have a user assigned.

Solution

In order to make an archive ownerless, all data related to previously assigned user needs to be deleted in database by the script below run over Directory Database. Once all related data will be deleted, an archive becomes ownerless after AD sync and EV sync.

--Users which left on-prem AD need to be removed from AS structures + update in some structures needed
--!!!NOT including Journal Explosion dependencies as deleting/updating JE entries is very risky!!!
set nocount off;
declare @SID varchar(128) 

declare ITEM_CURSOR  cursor for
select UserSid from [User] where sAMAccountName in ('XXX1','XXX2', 'XXX3') --or use different criteria in WHERE clause
open ITEM_CURSOR; 
fetch next from ITEM_CURSOR into @SID
while @@FETCH_STATUS = 0 
    begin
            -- delete UserProxyAddress dependency
            delete from [UserProxyAddress]
            where userSid in (@SID)
            print 'Deleted ' + @SID + ' from UserProxyAddress'

            -- delete ContainerToUser dependency
            delete from [ContainerToUser]
            where UserSid in (@SID)        
            print 'Deleted ' + @SID + ' from ContainerToUser'

            -- delete ExchangeMailbox dependency
            delete from [ExchangeMailbox]
            where OwnerUserSid in (@SID)    
            print 'Deleted ' + @SID + ' from ExchangeMailbox'

            -- delete UserToUserGroup dependency
            delete from [UserToUserGroup]
            where UserSid = @SID
            print 'Deleted ' + @SID + ' from UserToUserGroup'

            -- delete ContainerContentOperationsEnterpriseVault dependency
            delete from [ContainerContentOperationsEnterpriseVault]
            where ContainerId in (select a.ContainerId from [ContainerContentOperationsEnterpriseVault] a
            inner join [EVArchive] b on a.ContainerId = b.ContainerId
            where b.OwnerUserSid = @SID)
            print 'Deleted ' + @SID + ' from ContainerContentOperationsEnterpriseVault'

            -- update EVArchive dependency
            update x set OwnerUserSid = null, sid = null
            from [EVArchive] x
            where OwnerUserSid = @SID
            print 'Updated ' + @SID + ' from EVArchive'

            -- delete Office365LicenseToOffice365Mailbox dependency
            delete from [Office365LicenseToOffice365Mailbox]
            where Office365MailboxId in (select a.Office365MailboxId from [Office365LicenseToOffice365Mailbox] a
            inner join [Office365Mailbox] b on a.Office365MailboxId = b.Office365MailboxId
            where b.UserSid = @SID)
            print 'Deleted ' + @SID + ' from Office365LicenseToOffice365Mailbox'

            -- delete Office365Group dependency
            delete from [Office365Group] 
            where Office365MailboxId in (select a.Office365MailboxId FROM [Office365Group] a
            inner join [Office365Mailbox] b on a.Office365MailboxId = b.Office365MailboxId
            where b.UserSid = @SID)
            print 'Deleted ' + @SID + ' from Office365Group'

            -- delete Office365Mailbox dependency
            delete from [Office365Mailbox]
            where UserSid in (@SID)
            print 'Deleted ' + @SID + ' from Office365Mailbox'

            -- update SherpaHive dependency
            update x set OwnerUserSid = null
            from [SherpaHive] x
            where OwnerUserSid = @SID
            print 'Updated ' + @SID + ' from SherpaHive'

            -- delete WorkflowTriggerStatus dependency
            delete from [WorkflowTriggerStatus]
            where UserSid = @SID
            print 'Deleted ' + @SID + ' from WorkflowTriggerStatus'

            -- finaly delete user 
            delete from [User]
            where usersid in (@SID)
            print 'Deleted ' + @SID + ' from User'

            -- print details to message bar
            print '-----------------------------'
            print 'Finished ' + @SID; 
            print '-----------------------------'
            fetch next from ITEM_CURSOR into @SID; 
    end;
close ITEM_CURSOR;  
deallocate ITEM_CURSOR;

Note

Feel free to use any other attribute of the User table, that uniquely identifies the user that needs to be deleted in the line 5, instead of SamAccountName.

Print Friendly, PDF & Email
Updated on October 4, 2019

Was this article helpful?

Related Articles