how to list the SSRS permissions in each folder in the reporting services server?

Question

I have the following powershell script that downloads all the RDL from a reporting services server.

I am not the author to whom I am very thankful. It has been very useful for downloading the RDLs.

#note this is tested on PowerShell v2 and SSRS 2008 R2
#marcelo miorelli
#23-oct-2018
#How to Download All Your SSRS Report Definitions (RDL files) Using PowerShell
#Written by belle

#Here’s a short PowerShell script that :
#1. Connects to your report server
#2. Creates the same folder structure you have in your Report Server
#3. Download all the SSRS Report Definition (RDL) files into their respective folders

#In addition to backing up your Source Project, your ReportServer database, or good old RSScripter 
#(see http://sqlserver-indo.org/blogs/mca/archive/2009/03/08/extract-and-transfer-rdl-files-from-ssrs.aspx) 
#this is just another way you can “backup” or archive your reports.


[void][System.Reflection.Assembly]::LoadWithPartialName("System.Xml.XmlDocument");
[void][System.Reflection.Assembly]::LoadWithPartialName("System.IO");

$ReportServerUri = "http://qg-v-sqlrs-pr/Reportserver/Reportservice2005.asmx"      #"http://yourreportserver/ReportServer/ReportService2005.asmx";
$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential ;

#check out all members of $Proxy
#$Proxy | Get-Member
#http://msdn.microsoft.com/en-us/library/aa225878(v=SQL.80).aspx

#second parameter means recursive
$items = $Proxy.ListChildren("/", $true) | 
         select Type, Path, ID, Name | 
         Where-Object {$_.type -eq "Report"};

#create a new folder where we will save the files
#PowerShell datetime format codes http://technet.microsoft.com/en-us/library/ee692801.aspx

#create a timestamped folder, format similar to 2011-Mar-28-0850PM
$folderName = Get-Date -format "yyyy-MMM-dd-hhmmtt";
$fullFolderName = "D:\SSRS_Export\" + $folderName;
[System.IO.Directory]::CreateDirectory($fullFolderName) | out-null

foreach($item in $items)
{
    #need to figure out if it has a folder name
    $subfolderName = split-path $item.Path;
    $reportName = split-path $item.Path -Leaf;
    $fullSubfolderName = $fullFolderName + $subfolderName;
    if(-not(Test-Path $fullSubfolderName))
    {
        #note this will create the full folder hierarchy
        [System.IO.Directory]::CreateDirectory($fullSubfolderName) | out-null
    }

    $rdlFile = New-Object System.Xml.XmlDocument;
    [byte[]] $reportDefinition = $null;
    $reportDefinition = $Proxy.GetReportDefinition($item.Path);

    #note here we're forcing the actual definition to be 
    #stored as a byte array
    #if you take out the @() from the MemoryStream constructor, you'll 
    #get an error
    [System.IO.MemoryStream] $memStream = New-Object System.IO.MemoryStream(@(,$reportDefinition));
    $rdlFile.Load($memStream);

    $fullReportFileName = $fullSubfolderName + "\" + $item.Name +  ".rdl";
    #Write-Host $fullReportFileName;
    $rdlFile.Save( $fullReportFileName);

}

The problem that I am having on this particular server is that apparently I don't have enough permissions to download reports.

I would like to identify where exactly I am lacking permissions. How can I achieve that?

Alternatively, how could I list all the permissions in each folder in SSRS?

Here is the error message I get:

here is the text of the error message:

Exception calling "GetReportDefinition" with "1" argument(s): "The permissions granted to user 'mycompany\MMiorelli' are insufficient for performing this operation. ---> 
Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'mycompany\MMiorelli' are insufficient for performing this 
operation."
At line:62 char:5
+     $reportDefinition = $Proxy.GetReportDefinition($item.Path);
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SoapException

New-Object : Exception calling ".ctor" with "1" argument(s): "Buffer cannot be null.
Parameter name: buffer"
At line:68 char:43
+ ... memStream = New-Object System.IO.MemoryStream(@(,$reportDefinition)); ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [New-Object], MethodInvocationException
    + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

Exception calling "Load" with "1" argument(s): "Root element is missing."
At line:69 char:5
+     $rdlFile.Load($memStream);
+     ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "Save" with "1" argument(s): "Invalid XML document. The document does not have a root element."
At line:73 char:5
+     $rdlFile.Save( $fullReportFileName);
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

score 1 · Answer 1 · answered Feb 21 '20 at 17:14

Not a direct answer to this question, but as an alternative and certainly very useful,

I have followed the advise and link provided by Peter Vandivier in the comments above and doing some changes in the original script, like how to convert to VARBINARY(MAX) and even considered master.dbo.fn_varbintohexstr but it was too slow.

Here is the modified script, it gives all of the RDL files - it is an expensive query but it seems to work.

In my environment it took about a minute to return 348 reports. My powershell script returned 347 reports and complained about permissions, no complains about permissions here.


use ReportServer
go

with VarBinMax as ( 
    select 
         ItemID
        ,VarBinMax    = CONVERT(VARBINARY(MAX),[Content],1)
        ,HasBom       = CONVERT(BIT,IIF(
                        LEFT(CONVERT(VARBINARY(MAX),[Content],1),3)=0xEFBBBF,1,0))
        ,LenVarBinMax = LEN(CONVERT(VARBINARY(MAX),[Content],1))
        ,[Type]
    from [Catalog]
    where [Type] = 2
) -- select top(10) * from VarBinMax
, ContentNoBom as (
    select 
         ItemID
        ,ContentNoBom = CONVERT(VARBINARY(max),
                        IIF(HasBom=1,SUBSTRING(VarBinMax,4,LenVarBinMax),VarBinMax))
    from VarBinMax
)  
--select top(10) * from ContentNoBom
select 
     ItemID
    ,RdlXml  = CONVERT(XML,ContentNoBom)
    ,RdlText = CONVERT(NVARCHAR(MAX),CONVERT(XML,ContentNoBom))
into #RDL
from ContentNoBom cnb

select * 
from #RDL

score 0 · Accepted Answer · answered Oct 08 '24 at 15:31

this query by BateTech shows the permission per folder that I was looking for:


                        print @@servername
                        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
                        SET NOCOUNT OFF
select 
     [Path] = 
        case 
        when r.RoleFlags = 1 and c.Path is null
        then N'-Site Settings-' 
        else 
            case 
            when c.Path = N'' 
            then N'/' 
            else c.Path 
            end 
        end
    , MemberName = u.UserName
    , r.RoleName
    , RoleDescription = r.Description
    --, pur.PolicyID
    , RoleType = --Roles.RoleFlags values are from SecurityScope enum.
                 -- Used to interpret Roles.TaskMask field.
        case r.RoleFlags
        when 0 then 'Folder Security'
        when 1 then '-Site Settings-'
        when 2 then 'Model Item'
        else concat('Unknown: ', r.RoleFlags)
        end
    , ReportServerName = @@SERVERNAME
    , ExecTime = getdate()
    --, r.RoleFlags
from ReportServer.dbo.PolicyUserRole pur
   inner join ReportServer.dbo.Users u on pur.UserID = u.UserID
   inner join ReportServer.dbo.Roles r on pur.RoleID = r.RoleID
   --get the root catalog item for each policy
   outer apply (
        select top 1 c.Path, c.Name
        from ReportServer.dbo.Catalog c 
        where pur.PolicyID = c.PolicyID 
            and r.RoleFlags = 0 --Catalog items
        order by len(c.path) - len(replace(c.path, N'/', N'')) 
             -- get the highest level items where this policy is applied.
             -- Children that inherit from here will have 
             -- more "/"s than the parent.
            , c.path
    ) c
order by 
    RoleType,
    c.Path,
    u.UserName,
    r.RoleName

how to list the SSRS permissions in each folder in the reporting services server?

2 Answers2

Linked