SSRS allows you to query and modify aspects of our SSRS instance via web services. See https://msdn.microsoft.com/en-us/library/reportservice2010.aspx. We use these functions to modify permissions on the server.
Permissions can be queried via SQL, but you cannot edit permissions that way.
select distinct
c.Path
,c.Name
,u.UserName
,c.Type
from Catalog c
join Policies p
on c.PolicyID=p.PolicyID
join PolicyUserRole pur
on p.PolicyID=pur.PolicyID
join Users u
on pur.UserID=u.UserID
-- 1 = Folder
-- 2 = Reports
-- 5 = Data Source
-- 6 = Report Model
-- I think, could not find documentation
where c.Type in (1,2)
Through web services we can get that list, with two improvements:
If a AD account has been renamed, the Users.UserName field will still contain the original AD name. SSRS does not actually rely on this field, it relies on the SID. The SSRS web service calls will give you the actual domain name
You can see which permissions hare inherited using web services. So you can pull a list of all non-inherited permissions using them.
# Server to query
$url = 'https://ServerName/ReportServer/ReportService2010.asmx'
# open web service connection to SSRS
$ssrsProxy = New-WebServiceProxy -Uri $url -UseDefaultCredential
# get a list of all items: folders, reports, data sources, etc
$items = $ssrsProxy.ListChildren('/', $true)
# will will populate this with lists of who has permissions to which items
$UserPermissions = [System.Collections.ArrayList]@()
# iterate over each item
foreach ( $path in $items | Select-Object -Property Path, TypeName -Unique )
{
# get the permissions set on this item, and whether those permissions are inherited
$inheritParent = $true # will indictate if permissions for this item are inherited or not
$policies = $ssrsProxy.GetPolicies($path.Path, [ref]$inheritParent);
# we want to ignore inherited permissions. If we edit inherited permissions we break inheritance
if ( $inheritParent ) { continue }
# record who has permissions to this tiem
foreach ($policy in $policies )
{
$msg = "Path {0} : Type {1} : User {2}" -f $path.Path, $path.TypeName, $policy.GroupUserName
Write-Host $msg
[void]$UserPermissions.Add( @{"Path" = $path.Path; "Type" = $path.TypeName; "UserName" = $policy.GroupUserName } )
}
}
Editing Permissions
Once you have a policy object, you can modify it's members and roles, and update the permissions on the SSRS server using SetPolicies. Be careful about doing this on inherited policies though. If you edit inherited policies, you break inheritance. This can make it very difficult to manage permissions in the future.
The following removes permissions for winona\ecallahan from the SeaBattle folder, as an example:
$url = 'https://ServerName/ReportServer/ReportService2010.asmx'
$ssrsProxy = New-WebServiceProxy -Uri $url -UseDefaultCredential
# get the permissions set on this item, and whether those permissions are inherited
$inheritParent = $true # will indictate if permissions for this item are inherited or not
$policies = $ssrsProxy.GetPolicies('/SeaBattle', [ref]$inheritParent);
# we want to ignore inherited permissions. If we edit inherited permissions we break inheritance
if ( -not $inheritParent ) {
# change the Array into a Collection to we can remove items
$policies = {$policies}.Invoke()
# remove user from the collection
foreach ($policy in $policies | Where-Object { $_.GroupUserName -eq 'winona\ecallahan' } )
{
[void] $policies.Remove($policy)
}
# write the new permission back to the server
$ssrsProxy.SetPolicies('/SeaBattle', $policies)
}
Getting Report definitions
The GetItemDefinition method can be used to retrieve the report definition XML. A memory stream is return which needs to be loaded into an XML object. For example, the following code searches all query strings in each report for reference to VAL_COU_INSTR_UNIT.
# open web service connection to SSRS
$ssrsProxy = New-WebServiceProxy -Uri 'https://ServerName/ReportServer/ReportService2010.asmx' -UseDefaultCredential
# get a list of all items: folders, reports, data sources, etc
$items = $ssrsProxy.ListChildren('/', $true)
foreach ($r in $items)
{
if ($r.TypeName -ne 'Report') { continue }
$def = $ssrsProxy.GetItemDefinition($r.Path)
[System.IO.MemoryStream] $memStream = New-Object System.IO.MemoryStream(@(,$def));
$rdl = New-Object System.Xml.XmlDocument;
$rdl.Load($memStream)
foreach ($ds in $rdl.Report.DataSets.DataSet)
{
$sql = $ds.Query.CommandText
if ( $sql -like '*VAL_COU_INSTR_UNIT*' ){
$msg = '{0}: {1}' -f $r.Path, $ds.Name
Write-Host $msg
}
}
}
Searching Data Source properties
# open web service connection to SSRS
$ssrsProxy = New-WebServiceProxy -Uri $url -UseDefaultCredential
# get a list of all items: folders, reports, data sources, etc
$items = $ssrsProxy.ListChildren('/', $true)
foreach ($d in $items | Where-object TypeName -eq 'DataSource')
{
$t = $ssrsProxy.GetDataSourceContents($d.Path)
if ($t.UserName -match 'housing'){
'{0} - {1}' -f $d.Path, $t.UserName
}
}