PowerShell script to migrate permission from one SQL Server Reporting Server to another...

 Below PowerShell script can be used to migrate the SQL Server Report Server permission from one SQL Server Reporting Server to another:

#Source Report Server

$SourceReportServerUri = 'http://sourceserver/ReportServer/ReportService2010.asmx?wsdl'

$InheritParent = $true

$SSRSroot = "/"

$rsProxySource = New-WebServiceProxy -Uri $SourceReportServerUri -UseDefaultCredential

$ItemListSource = $rsProxySource.ListChildren($SSRSroot, $InheritParent) | Select -Property Path, TypeName | Select Path

#Iterate through every Item 

foreach($ItemSource in $ItemListSource)

{

#Return all policies on this Item

$PoliciesSource = $rsProxySource.GetPolicies( $ItemSource.Path, [ref] $InheritParent )

#Iterate through every Policy

foreach($rsPolicySource in $PoliciesSource)

{

        $RoleNames = $rsPolicySource.Roles

        foreach($Role in $RoleNames)

        {

            $RoleName = $Role.Name

            $GroupUserNames = $rsPolicySource.GroupUserName

            foreach($GroupUserName in $GroupUserNames)

            {

                #Destination Report Server

                $DestinationReportServerUri = 'http://destinationserver/ReportServer_SSRS2017/ReportService2010.asmx?wsdl'

                $rsProxyDestination = New-WebServiceProxy -Uri $DestinationReportServerUri -UseDefaultCredential

                $type = $rsProxyDestination.GetType().Namespace;

                $policyType = "{0}.Policy" -f $type;

                $roleType = "{0}.Role" -f $type;

                $items = $rsProxyDestination.ListChildren("/", $true) | `

                         SELECT TypeName, Path, ID, Name | `

                         Where-Object {$_.Path -eq $ItemSource.Path}

                #Iterate through every folder 

                foreach($item in $items)

                {

                $PoliciesDestination = $rsProxyDestination.GetPolicies($Item.Path, [ref]$InheritParent)

         #Return all policies that contain the user/group we want to add

            $PolicyDestination = $PoliciesDestination | 

                Where-Object { $_.GroupUserName -eq $GroupUserName } | 

                Select-Object -First 1

            #Add a new policy if doesn't exist

            if (-not $PolicyDestination) 

            {

                $PolicyDestination = New-Object ($policyType)

                $PolicyDestination.GroupUserName = $GroupUserName

                $PolicyDestination.Roles = @()

            #Add new policy to the folder's policies

                $PoliciesDestination += $PolicyDestination

            }


            #Add the role to the new Policy

            $r = $PolicyDestination.Roles |

                    Where-Object { $_.Name -eq $RoleName } |

                    Select-Object -First 1

                if (-not $r) 

                {

                    $r = New-Object ($roleType)

                    $r.Name = $RoleName

                    $PolicyDestination.Roles += $r

                }        

             #Set folder policies

             $rsProxyDestination.SetPolicies($Item.Path, $PoliciesDestination);

                }

            }

        }

}

}


Hope this will be helpful...

No comments:

Post a Comment

How to run UPDATE/INSERT/DELETE Statements on Azure SQL Database in Microsoft Fabric Notebook...

You can run UPDATE/INSERT/DELETE Statements on Azure SQL Database in Microsoft Fabric Notebook using Python SQL Driver - pyodbc.  For the Fa...