Finds items with a specified user or role assigned and transfers to another role in Sitecore PowerShell Extensions.

TransferAssignedSecurityToAnotherRole.ps1

<#
    .SYNOPSIS
        Find all items with the specified user or role assigned.
        
    .DESCRIPTION
        This report helps identify all of the items with a specific user or role assigned and transfers to another role.
        
    .NOTES
        Michael West
#>

Import-Function -Name Invoke-SqlCommand

$scriptItem = Get-Item -Path $SitecoreCommandPath
$icon = $scriptItem.Appearance.Icon -replace "16x16","32x32"
$settings = @{
    Title = "Report Filter"
    Width = "450"
    Height = "250"
    OkButtonName = "Proceed"
    CancelButtonName = "Abort"
    Description = "Filter items explicitly referencing the specified role."
    Parameters = @(
        @{ Name = "userOrRoleOwner"; Title="Current User or Role"; Tooltip="Tooltip for role"; Editor="user role"; Domain="sitecore"; },
        @{ Name = "roleNewOwner"; Title="New Role"; Tooltip="Tooltip for role"; Editor="role"; Domain="sitecore"; }
    )
    Icon = $icon
}

$result = Read-Variable @settings
if($result -ne "ok") {
    Exit
}

$connection = [Sitecore.Configuration.Settings]::GetConnectionString("master")

$securityFieldId = [Sitecore.FieldIDs]::Security

# Find all the items which explicitly hae security assigned.
$query = @"
SELECT [ItemId], [Value]
  FROM [dbo].[SharedFields]
  WHERE [FieldId] = '$($securityFieldId.ToString())'
	AND [Value] <> ''
"@
$records = Invoke-SqlCommand -Connection $connection -Query $query

$reportProperties = @{
    Property = @("Name", "Id", "ItemPath", @{Name="Security";Expression={$_."__Security"}})
    Title = "Items with security reassigned"
    InfoTitle = "Items with security reassigned"
    InfoDescription = "Items with the account set to '$($userOrRoleOwner)' have been transferred to $($roleNewOwner)."
}
$records | Where-Object { $_.Value -match [System.Text.RegularExpressions.Regex]::Escape($userOrRoleOwner) } |
    ForEach-Object { 
        $item = Get-Item -Path "master:" -ID "$($_.ItemId.ToString())" 
        $item."__Security" = $item."__Security".Replace("$userOrRoleOwner","$roleNewOwner")
        $item
    } | Show-ListView @reportProperties

Close-Window