Chat now with support
Chat with Support

On Demand Migration Current - Active Directory Express User Guide

SQL Repermission Tool

Use the SQL Repermission Tool to update a SQL Server’s Source AD Windows Authentication Group Login permissions and their associated database users for the Target domain to which the user and group objects have been migrated.

Prior to re-permissioning SQL servers, accounts must be migrated and Mapping Files created.

  1. Download the SQL Repermission.msi file from the Downloads screen.
  2. Run the installer on a machine that will have access to the SQL Server instance and databases to be re-permissioned.

  3. The Welcome screen appears. Click Next to continue.

  4. The "Ready to Install the Program" screen appears. Click Install to begin the installation.

  5. When the installation completes, the "InstallShield Wizard Completed" message appears. Click Finish to close the wizard.

  6. Copy the map.usr and map.gg files created to the SQL Repermission installation folder, default location is "C:\Program Files\Quest\SQL Repermission".

  7. Launch the SQL Repermission tool from the Apps screen or the Start menu.

  8. Select the SQL Logon Method. If SQL Server Authentication is selected, enter the Username and Password. If Windows Authentication will be used, you must be logged onto the machine with an account that has access to the SQL Server instance and databases to be re-permissioned.

  9. Click on Browse to select the SQL Server instance to be re-permissioned.

  10. Select the SQL server instance from the list and click on OK.

  11. Click on the Connect button. If connection is successful, the Logging message should read “SQL Server Connected” and there should be databases available to pick from in the drop-down list.

    Note: If the login information is incorrect, the following error will be received. The credentials to connect to the SQL server instance should be corrected, and then the Connect should be retried.

  12. Once successfully connected to the SQL Server instance, in the drop down select either <ALL> or a specific database that needs to be re-permissioned.

  13. Click on the Generate SQL button. If the Open script in Notepad option is left checked, the results will be displayed when completed.

  14. The Logging information will display the name of the SQL file created in the directory that the application is located.

            Logging: C:\Program Files\Quest\SQL Repermission\SQLPermissions_20150820112148.sql

  15. If the option to open the file in Notepad was not checked, navigate to where the file was saved and right-click and choose Open with Notepad.

  16. The results of the process can be reviewed prior to actually executing it on the SQL Server instance.

  17. When the SQL file has been reviewed, either open the file in SQL Server Management Studio or create a new Query in SQL Server Management Studio and copy/paste the contents of the file into the Query.

  18. Execute the Query and the new target credentials will be created.

CSV Import File Script

Import File Script Guide
######Guide on how to search objects to create mapping files for ADExpress######
--------------------------------------------------------------------------------
 
############ A. Helper script description
 
script has functions which helps user to convert the AD object search results into .csv mapping files
 
1. ADConvertToCsv
- helps organize the search result into .csv list files or .csv mapping files
- called by adding to AD object search pipeline
<AD object search command> | ADConvertToCsv
(AD object search commands are explained below)
- will create .csv file to the current script directory from the search result
- if "-Path" is specified, will create file in the path (... | ADConvertToCsv -Path "C:\Users\userx\objects.csv")
- if "-Append" switch is selected, will append existing file (must be called with -Path)
- supports AD, azureAD graph and microsoft graph search command results
- there are two main modes:
    -using "-Source" switch: creates .csv mapping file with all objects returned by the AD object search command as Source objects. User must manually add the Target objects to complete mapping file.
    -default (without "-Source" switch): creates .csv object file with all objects returned by the AD object search command. This object file cannot be used as mapping file. In some cases it contains more information about objects than mapping file.
 
Main way to use it :
 
    1. Create .csv mapping template file with filled source objects:
        <AD object search command> | ADConvertToCsv -Source
 
    2. Create .csv object file with target objects:
        <AD object search command> | ADConvertToCsv
 
    3. Manually add the target objects to the source .csv mapping template file
 
    4. Upload mapping file
 
 
2. ADMapLocalUsers, ADMapLocalGroups
- helps with mapping of the objects by matching objects with the same SamAccountName
- only works for Local->Local mapping
- functions will create .csv mapping files for Users and Groups respectively in the same directory
- requires two .csv object files (created using ADConvertToCsv - default mode, without "-Source")
- objects that dont have matching SamAccountName will be put to the bottom of the mapping file and will need to be adjusted before uploading mapping file
 
Main way to use it:
 
    1. Create .csv object file with source objects:
        <AD object search command> | ADConvertToCsv
 
    2. Create .csv object file with target objects:
        <AD object search command> | ADConvertToCsv
 
    3. Map objects 
        ADMapLocalUsers <source_object_file_path> <target_object_file_path>
        or
        ADMapLocalGroups <source_object_file_path> <target_object_file_path>
 
 
###################### B. ADDITIONAL GUIDES
 
#####1. prepare powershell window
for getting AD local objects 
    - available by default
    - any version (https://learn.microsoft.com/en-us/powershell/module/activedirectory/?view=windowsserver2022-ps&source=recommendations)
 
for getting cloud AD objects via microsoft graph 
    - https://learn.microsoft.com/en-us/powershell/microsoftgraph/installation?view=graph-powershell-1.0
    - powershell version 5.1 and higher (powershell 7 recommended)
    - powershell 7 installation guide https://learn.microsoft.com/en-us/powershell/scripting/install/installing-powershell-on-windows?view=powershell-7.4
    -docs state it should work with 5.1 but 7 is recommended. i havent made it work with 5.1
 
(DEPRECATED)for getting AD cloud objects via azureAd graph 
    - https://learn.microsoft.com/en-us/powershell/azure/active-directory/install-adv2?view=azureadps-2.0
    - powershell version 5.1 and lower 
 
 
#####2. Import script to the current powershell session
- run powershell
- move to directory where helper script is located (find directory/folder in UI -> shift-rightmouseclick -> "copy as path"
cd <directory_path>
- load script
. .\BT-ADObjectMapper.ps1
 
 
#####3. AD Object search commands
 
####3.1 LOCAL
##3.1.0 Filtering Local objects
-Filter <query>
specifies query string that retrieves AD objects
 
-SearchBase <path> 
specifies Active Directory path to search under
 
 
#examples:
Get-ADUser -Filter * -SearchBase "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM"
-gets all users (specified by -Filter *) in the container "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM"
 
Get-ADUser -Filter 'SamAccountName -like "*test"' -SearchBase "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM"
-gets all users, which have SamAccountName ending with "test" in the container "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM"
 
*more in doc links
 
##3.1.1 USER
function:
Get-AdUser (https://learn.microsoft.com/en-us/powershell/module/activedirectory/get-aduser?view=windowsserver2022-ps)
example:
Get-ADUser -Filter * -SearchBase "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM"| ADConvertToCsv
 
##3.1.2 GROUP
function:
Get-ADGroup (https://learn.microsoft.com/en-us/powershell/module/activedirectory/get-adgroup?view=windowsserver2022-ps)
example:
Get-ADGroup -Filter * -SearchBase "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM" | ADConvertToCsv
 
##3.1.3 DEVICE 
function:
Get-ADComputer (https://learn.microsoft.com/en-us/powershell/module/activedirectory/get-adcomputer?view=windowsserver2022-ps)
example:
Get-ADComputer -Filter * -SearchBase "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM" | ADConvertToCsv
 
 
 
####3.2 CLOUD using azureAD (deprecated https://techcommunity.microsoft.com/t5/microsoft-entra-blog/important-azure-ad-graph-retirement-and-powershell-module/ba-p/3848270)
##3.1.0 Filtering Cloud objects using azureAD
before calling any function we need to call Connect-AzureAD to connect to the azure Azure Active Directory cmdlet (https://learn.microsoft.com/en-us/powershell/module/azuread/connect-azuread?view=azureadps-2.0)
 
-All 
returns all users
-Filter <filter>
allows for OData v3.0 filtering (https://www.odata.org/documentation/odata-version-3-0/odata-version-3-0-core-protocol/#queryingcollections)
-SearchString <string>
gets all objects that match the search string against the first characters in DisplayName or UserPrincipalName
#examples:
Get-AzureADUser -Filter "userPrincipalName eq 'jondoe@contoso.com'"
-gets all users that have userPrincipalName jondoe@contoso.com
Get-AzureADGroup -SearchString "Special"
-gets all groups that have display names starting with "Special"
Get-AzureADDevice -Filter "startswith(DeviceOSType,'Windows')"
-gets all devices that have DeviceOSType starting with "Windows"
 
##3.2.1 USER
function:
Get-AzureADUser (https://learn.microsoft.com/en-us/powershell/module/azuread/get-azureaduser?view=azureadps-2.0)
example:
Connect-AzureAD (interactive login)
Get-AzureADUser -All:$true | ADConvertToCsv
 
##3.2.2 GROUP
function:
Get-AzureADGroup (https://learn.microsoft.com/en-us/powershell/module/azuread/get-azureadgroup?view=azureadps-2.0)
example:
Connect-AzureAD (interactive login)
Get-AzureADGroup -All:$true | ADConvertToCsv
 
##3.2.3 DEVICE 
function:
Get-AzureADDevice (https://learn.microsoft.com/en-us/powershell/module/azuread/get-azureaddevice?view=azureadps-2.0)
example:
Connect-AzureAD (interactive login)
Get-AzureADDevice -All:$true | ADConvertToCsv
 
 
####3.3 CLOUD using Microsoft Graph (recommended)
 
##3.1.0 Filtering Cloud objects using Microsoft Graph
before calling any function we need to call Connect-MgGraph to connect to the azure entra id (https://learn.microsoft.com/en-us/powershell/module/microsoft.graph.authentication/connect-mggraph?view=graph-powershell-1.0)
 
-All
returns all users
 
-Filter <filter>
allows for OData v3.0 filtering (https://www.odata.org/documentation/odata-version-3-0/odata-version-3-0-core-protocol/#queryingcollections)
 
-Search <phrase>
search items by search phrases
 
#examples:
Get-MgUser -Filter "startsWith(DisplayName, 'a')"
-search users which displayname starts with "a" (case-insensitive)
Get-MgUser -Search '"Mail:Peter"'
-search users which email adress contains "Peter"
 
*more in doc links
 
##3.3.1 USER
function:
Get-MgUser (https://learn.microsoft.com/en-us/powershell/module/microsoft.graph.users/get-mguser?view=graph-powershell-1.0&preserve-view=true)
example:
Connect-MgGraph -Scopes 'User.Read.All' (to get permissions and choose entra id- interactive login)
Get-MgUser -All | ADConvertToCsv
 
##3.3.2 GROUP
function:
Get-MgGroup (https://learn.microsoft.com/en-us/powershell/module/microsoft.graph.groups/get-mggroup?view=graph-powershell-1.0)
example:
Connect-MgGraph -Scopes 'Group.Read.All' (to get permissions and choose entra id- interactive login)
Get-MgGroup -All | ADConvertToCsv
 
##3.3.3 DEVICE
function:
Get-MgDevice (https://learn.microsoft.com/en-us/powershell/module/microsoft.graph.identity.directorymanagement/get-mgdevice?view=graph-powershell-1.0)
example:
Connect-MgGraph -Scopes 'Device.Read.All' (to get permissions and choose entra id- interactive login)
Get-MgDevice -All | ADConvertToCsv
 
##to switch entra id
 
-disconnect
Disconnect-MgGraph
 
-connect to a different one
Connect-MgGraph -Scopes <required_scopes>

 

Import File Script
function ADConvertToCsv() #Used to convert AD(onprem/cloud) objects (users,groups,devices/computers) to a .csv list for AD Express mapping file.
{
    Param(
        [Parameter(
            Mandatory=$true, 
            ValueFromPipeline=$true)]
        $InputObject, #Can take input from pipeline
 
        [Parameter(Mandatory=$false)] 
        [string]$Path, #Path to output .csv file, if not provided, output will be created in current ps directory
 
        [Parameter(Mandatory=$false)]
        [switch]$Append, #Appends existing .csv file (-Path must be specified)
 
        [Parameter(Mandatory=$false)]
        [switch]$Source #To create template mapping file with input as source objects
    )
    Begin
    {
        #append or rewrite
        if ($Append)
        {
            if (!$Path)
            {
                Throw "[ERROR]: Path must be specified (-Path) in order to append existing file."
            }
            elseif (!(Test-Path $Path))
            {
                Throw "[ERROR]: File does not exist. It needs to exist in order to be appended."
            }
 
            $saveOperationType = @{ Append = $true }
        }
        else {
            $saveOperationType = @{ Force = $true }
        }
 
        #specify output path
        $outputPath = ""
        if ($Path)
        {
            $outputPath = $Path
        }
        else
        {
            if ($Source)
            {
                $outputPath = "$PSScriptRoot\object_mapping_file_" + [DateTime]::Now.ToString('yyyy_MM_dd_HH_mm_ss') + ".csv"
            }
            else 
            {
                $outputPath = "$PSScriptRoot\object_list_" + [DateTime]::Now.ToString('yyyy_MM_dd_HH_mm_ss') + ".csv"
            }
        }
 
        $inputList = @()
        $inputType = ""
    }
    Process
    {
        if ($InputObject)
        {
            #make sure the type is allowed and all objects are of same type
            if ($inputType -eq "")
            {
                
                $inputType = $InputObject.GetType().FullName
 
                if ($inputType -notin @("Microsoft.ActiveDirectory.Management.ADUser", 
                                        "Microsoft.ActiveDirectory.Management.ADGroup", 
                                        "Microsoft.ActiveDirectory.Management.ADComputer", 
                                        "Microsoft.Graph.PowerShell.Models.MicrosoftGraphUser",
                                        "Microsoft.Graph.PowerShell.Models.MicrosoftGraphGroup",
                                        "Microsoft.Graph.PowerShell.Models.MicrosoftGraphDevice",
                                        "Microsoft.Open.AzureAD.Model.User", 
                                        "Microsoft.Open.AzureAD.Model.Group", 
                                        "Microsoft.Open.AzureAD.Model.Device"))
                {
                    Throw "[ERROR]: Type $inputType is not one of the accepted types."
                }
            }
            elseif ($InputObject.GetType().FullName -ne $inputType)
            {
                Throw "[ERROR]: Type $($InputObject.GetType().FullName) is not the same type as previous $inputType."
            }
 
            $inputList += $InputObject
        }
    }
    End 
    {
        if ($inputList.Count -eq 0)
        {
            Write-Host "[INFO]: There are no records. Csv file will not be created."
            return
        }
 
        #transform input into .csv file
        switch ($inputType)
        {
            "Microsoft.ActiveDirectory.Management.ADUser" 
            {
                if ($Source)
                {
                    $inputList | 
                        Select-Object -Property @{Name="SourceObjectId"; Expression={$_.ObjectGUID}}, `
                            @{Name="SourceUserPrincipalName (Optional)"; Expression={$_.UserPrincipalName}}, `
                            @{Name="SourceSamAccountName (Optional)"; Expression={$_.SamAccountName}}, `
                            @{Name="TargetObjectId"; Expression={""}}, `
                            @{Name="TargetUserPrincipalName (Optional)"; Expression={""}}, `
                            @{Name="TargetSamAccountName (Optional)"; Expression={""}}, `
                            @{Name="Comments (Optional)"; Expression={""}} |
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
                else 
                {
                    $inputList | 
                        Select-Object -Property @{Name="ObjectId"; Expression={$_.ObjectGUID}}, `
                            @{Name="UserPrincipalName"; Expression={$_.UserPrincipalName}}, `
                            @{Name="SamAccountName"; Expression={$_.SamAccountName}} | 
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
            }
 
            "Microsoft.ActiveDirectory.Management.ADGroup" 
            {
                if ($Source)
                {
                    $inputList | 
                        Select-Object -Property @{Name="SourceObjectId"; Expression={$_.ObjectGUID}}, `
                            @{Name="SourceSamAccountName (Optional)"; Expression={$_.SamAccountName}}, `
                            @{Name="TargetObjectId"; Expression={""}}, `
                            @{Name="TargetSamAccountName (Optional)"; Expression={""}}, `
                            @{Name="Comments (Optional)"; Expression={""}} |
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
                else 
                {
                    $inputList | 
                        Select-Object -Property @{Name="ObjectId"; Expression={$_.ObjectGUID}}, `
                            @{Name="SamAccountName"; Expression={$_.SamAccountName}} | 
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
            }
            "Microsoft.ActiveDirectory.Management.ADComputer" {
                if ($Source)
                {
                    $inputList | 
                        Select-Object -Property @{Name="SourceObjectId"; Expression={$_.ObjectGUID}}, `
                            @{Name="SourceSamAccountName (Optional)"; Expression={$_.SamAccountName}}, `
                            @{Name="Comments (Optional)"; Expression={""}} | 
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
                else 
                {
                    $inputList | 
                        Select-Object -Property @{Name="ObjectId"; Expression={$_.ObjectGUID}}, `
                            @{Name="SamAccountName"; Expression={$_.SamAccountName}} | 
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
            }
            "Microsoft.Graph.PowerShell.Models.MicrosoftGraphUser"{
                if ($Source)
                {
                    $inputList | 
                        Select-Object -Property @{Name="SourceObjectId"; Expression={$_.Id}}, `
                            @{Name="SourceUserPrincipalName (Optional)"; Expression={$_.UserPrincipalName}}, `
                            @{Name="SourceSamAccountName (Optional)"; Expression={$_.OnPremisesSamAccountName}}, `
                            @{Name="TargetObjectId"; Expression={""}}, `
                            @{Name="TargetUserPrincipalName (Optional)"; Expression={""}}, `
                            @{Name="TargetSamAccountName (Optional)"; Expression={""}}, `
                            @{Name="Comments (Optional)"; Expression={""}} |
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
                else 
                {
                    $inputList | 
                        Select-Object -Property @{Name="ObjectId"; Expression={$_.Id}}, `
                            @{Name="UserPrincipalName"; Expression={$_.UserPrincipalName}}, `
                            @{Name="SamAccountName"; Expression={$_.OnPremisesSamAccountName}} | 
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
            }
            "Microsoft.Graph.PowerShell.Models.MicrosoftGraphGroup"{
                if ($Source)
                {
                    $inputList | 
                        Select-Object -Property @{Name="SourceObjectId"; Expression={$_.Id}}, `
                            @{Name="SourceSamAccountName (Optional)"; Expression={$_.OnPremisesSamAccountName}}, `
                            @{Name="TargetObjectId"; Expression={""}}, `
                            @{Name="TargetSamAccountName (Optional)"; Expression={""}}, `
                            @{Name="Comments (Optional)"; Expression={""}} |
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
                else 
                {
                    $inputList | 
                        Select-Object -Property @{Name="ObjectId"; Expression={$_.Id}}, `
                            @{Name="SamAccountName"; Expression={$_.OnPremisesSamAccountName}}, `
                            @{Name="DisplayName"; Expression={$_.DisplayName}}, `
                            @{Name="EmailAddress"; Expression={$_.Mail}} | 
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
            }
            "Microsoft.Graph.PowerShell.Models.MicrosoftGraphDevice"{
                if ($Source)
                {
                    $inputList | 
                        Select-Object -Property @{Name="SourceObjectId"; Expression={$_.Id}}, `
                            @{Name="SourceSamAccountName (Optional)"; Expression={$_.OnPremisesSamAccountName}}, `
                            @{Name="Comments (Optional)"; Expression={""}} | 
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
                else 
                {
                    $inputList | 
                        Select-Object -Property @{Name="ObjectId"; Expression={$_.Id}}, `
                            @{Name="SamAccountName"; Expression={$_.OnPremisesSamAccountName}}, `
                            @{Name="DisplayName"; Expression={$_.DisplayName}} | 
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
            }
            "Microsoft.Open.AzureAD.Model.User" {
                if ($Source)
                {
                    $inputList | 
                        Select-Object -Property @{Name="SourceObjectId"; Expression={$_.ObjectId}}, `
                            @{Name="SourceUserPrincipalName (Optional)"; Expression={$_.UserPrincipalName}}, `
                            @{Name="SourceSamAccountName (Optional)"; Expression={""}}, `
                            @{Name="TargetObjectId"; Expression={""}}, `
                            @{Name="TargetUserPrincipalName (Optional)"; Expression={""}}, `
                            @{Name="TargetSamAccountName (Optional)"; Expression={""}}, `
                            @{Name="Comments (Optional)"; Expression={""}} |
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
                else 
                {
                    $inputList | 
                        Select-Object -Property @{Name="ObjectId"; Expression={$_.ObjectId}}, `
                            @{Name="UserPrincipalName"; Expression={$_.UserPrincipalName}}, `
                            @{Name="SamAccountName"; Expression={""}} | 
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
            }
            "Microsoft.Open.AzureAD.Model.Group" {
                if ($Source)
                {
                    $inputList | 
                        Select-Object -Property @{Name="SourceObjectId"; Expression={$_.ObjectId}}, `
                            @{Name="SourceSamAccountName (Optional)"; Expression={""}}, `
                            @{Name="TargetObjectId"; Expression={""}}, `
                            @{Name="TargetSamAccountName (Optional)"; Expression={""}}, `
                            @{Name="Comments (Optional)"; Expression={""}} |
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
                else 
                {
                    $inputList | 
                        Select-Object -Property @{Name="ObjectId"; Expression={$_.ObjectId}}, `
                            @{Name="SamAccountName"; Expression={""}}, `
                            @{Name="DisplayName"; Expression={$_.DisplayName}}, `
                            @{Name="EmailAddress"; Expression={$_.Mail}} | 
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
            }
            "Microsoft.Open.AzureAD.Model.Device" {
                if ($Source)
                {
                    $inputList | 
                        Select-Object -Property @{Name="SourceObjectId"; Expression={$_.ObjectId}}, `
                            @{Name="SourceSamAccountName (Optional)"; Expression={""}}, `
                            @{Name="Comments (Optional)"; Expression={""}} | 
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
                else 
                {
                    $inputList | 
                        Select-Object -Property @{Name="ObjectId"; Expression={$_.ObjectId}}, `
                            @{Name="SamAccountName"; Expression={""}}, `
                            @{Name="DisplayName"; Expression={$_.DisplayName}} | 
                                Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation @saveOperationType
                }
            }
            default {
                Throw "[ERROR]: Type $inputType is not one of the accepted types."
            }
        }
        
        if ($Append)
        {
            Write-Host "[INFO]: File '$($outputPath)' appended"
        }
        else
        {
            Write-Host "[INFO]: File '$($outputPath)' created"
        }
    }
}
 
 
function ADMapLocalUsers($sourceUsersPath, $targetUsersPath) #Map two user files based on SamAccountName
{
    $sourceUsers = Import-Csv -Path $sourceUsersPath -Delimiter ","
    $targetUsers = Import-Csv -Path $targetUsersPath -Delimiter ","
    $mappedUsers = @()
    $nonMappedUsers = @()
 
    #for each source user, try to find target user via SamAccountName
    $sourceUsers | % {
        $sourceObject = $_
        $targetMatch = $null
        if ($sourceObject.SamAccountName -ne "")
        {
            $targetMatch = $targetUsers | Where-Object {$_.SamAccountName -eq $sourceObject.SamAccountName}
        }
        
        if ($targetMatch) #if found, create match record
        {
            if ($targetMatch.Count -gt 1)
            {
                throw "[ERROR]: Target User SamAccountName is not unique '$($sourceObject.SamAccountName)'"
            }
 
            $mappedUsers += [PSCustomObject]@{
                SourceObjectId = $sourceObject.ObjectId
                SourceUserPrincipalName = $sourceObject.UserPrincipalName
                SourceSamAccountName = $sourceObject.SamAccountName
                TargetObjectId = $targetMatch.ObjectId
                TargetUserPrincipalName = $targetMatch.UserPrincipalName
                TargetSamAccountName = $targetMatch.SamAccountName
                Comments = ""
            }
        }
        else #if havent found, create record without 
        {
            $nonMappedUsers += [PSCustomObject]@{
                SourceObjectId = $sourceObject.ObjectId
                SourceUserPrincipalName = $sourceObject.UserPrincipalName
                SourceSamAccountName = $sourceObject.SamAccountName
                TargetObjectId = ""
                TargetUserPrincipalName = ""
                TargetSamAccountName = ""
                Comments = ""
            }
        }
    }
 
    #save target users which dont have source user match
    $sourceUsersWithSam = $sourceUsers | Where-Object {$_.SamAccountName -ne ""} 
    $targetUsersWithoutSourceSam = $targetUsers | Where-Object { $_.SamAccountName -notin $sourceUsersWithSam.SamAccountName}
    $targetUsersWithoutSourceSam | % {
        $nonMappedUsers += [PSCustomObject]@{
            SourceObjectId = ""
            SourceUserPrincipalName = ""
            SourceSamAccountName = ""
            TargetObjectId = $_.ObjectId
            TargetUserPrincipalName = $_.UserPrincipalName
            TargetSamAccountName = $_.SamAccountName
            Comments = ""
        }
    }
 
    #save
    $mappedUsers += $nonMappedUsers
    $outputPath = "$PSScriptRoot\user_map_" + [DateTime]::UtcNow.ToString('yyyy_MM_dd_HH_mm_ss') + ".csv"
    $mappedUsers | Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation
    Write-Host "[INFO]: File '$($outputPath)' created"
}
function ADMapLocalGroups($sourceGroupsPath, $targetGroupsPath) #Map two group files based on SamAccountName
{
    $sourceGroups = Import-Csv -Path $sourceGroupsPath -Delimiter ","
    $targetGroups = Import-Csv -Path $targetGroupsPath -Delimiter ","
    $mappedGroups = @()
    $nonMappedGroups = @()
 
    #for each source group, try to find target group via SamAccountName
    $sourceGroups | % {
        $sourceObject = $_
        $targetMatch = $null
        if ($sourceObject.SamAccountName -ne "")
        {
            $targetMatch = $targetGroups | Where-Object {$_.SamAccountName -eq $sourceObject.SamAccountName}
        }
        
        if ($targetMatch) #if found, create match record
        {
            if ($targetMatch.Count -gt 1)
            {
                throw "Target Group SamAccountName is not unique '$($sourceObject.SamAccountName)'"
            }
 
            $mappedGroups += [PSCustomObject]@{
                SourceObjectId = $sourceObject.ObjectId
                SourceSamAccountName = $sourceObject.SamAccountName
                TargetObjectId = $targetMatch.ObjectId
                TargetSamAccountName = $targetMatch.SamAccountName
                Comments = ""
            }
        }
        else #if havent found, create record without 
        {
            $nonMappedGroups += [PSCustomObject]@{
                SourceObjectId = $sourceObject.ObjectId
                SourceSamAccountName = $sourceObject.SamAccountName
                TargetObjectId = ""
                TargetSamAccountName = ""
                Comments = ""
            }
        }
    }
 
    #save target groups which dont have source group match
    $sourceGroupsWithSam = $sourceGroups | Where-Object {$_.SamAccountName -ne ""} 
    $targetGroupsWithoutSourceSam = $targetGroups | Where-Object { $_.SamAccountName -notin $sourceGroupsWithSam.SamAccountName}
    $targetGroupsWithoutSourceSam | % {
        $nonMappedGroups += [PSCustomObject]@{
            SourceObjectId = ""
            SourceSamAccountName = ""
            TargetObjectId = $_.ObjectId
            TargetSamAccountName = $_.SamAccountName
            Comments = ""
        }
    }
 
    #save
    $mappedGroups += $nonMappedGroups
    $outputPath = "$PSScriptRoot\group_map_" + [DateTime]::UtcNow.ToString('yyyy_MM_dd_HH_mm_ss') + ".csv"
    $mappedGroups | Export-Csv -Path $outputPath -Delimiter "," -NoTypeInformation
    Write-Host "[INFO]: File '$($outputPath)' created"
}
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating