SSRS Permissions

Overview

SSRS Permissions shows who has been given what on a report server, and more usefully, where somebody broke inheritance to give it to them.

Report server permissions work the way file system permissions do. A folder carries a policy, everything under it inherits that policy, and an item that has been given a policy of its own no longer inherits. Those breaks are the interesting rows: they are how one report inside a folder everybody can read ends up readable by one person, and how one report inside a locked folder ends up readable by everybody.

The page puts together:

  • Every inheritance break, with the item it is on and who holds what there.
  • Every assignment of a role that can change what other people see: Content Manager, Publisher and System Administrator.
  • The site wide assignments, System Administrator and System User, which belong to the report server itself rather than to any folder.
  • Principals that hold no assignment, own no content and no subscriptions, which are usually the leftovers of people who have left.

It shows assignments, not effective permissions. A Windows group named here can contain anybody, and the report server database does not know who.

Where to find it

Only shown for a database holding a Reporting Services catalog.

  • Tree: expand the report server database, then Real Time > SSRS > Permissions.
  • SSRS Server Configuration: the Permissions toolbar button.
  • SSRS Catalog Inventory: right click an item that has permissions of its own and choose Who can see this.
The SSRS Permissions report: items that break inheritance above the grid of every assignment
The chart holds only the items with permissions of their own; the grid holds every assignment.

Requirements

  • A Reporting Services catalog database. The SSRS pages are offered when dbo.ExecutionLog and dbo.Catalog both exist.
  • SELECT on dbo.PolicyUserRole, dbo.Policies, dbo.Catalog, dbo.Users, dbo.Roles and dbo.Subscriptions.
  • The query is given 90 seconds.

The two views

The toolbar has a two part switch, By item and By person, and buttons that open SSRS Catalog Inventory and SSRS Server Configuration.

By item

The SSRS permissions chart
One bar per assignment on an item that no longer inherits from its folder, powerful roles first.

One bar per assignment on an item that has permissions of its own, not counting the root folder. Assignments of Content Manager, Publisher and System Administrator come first, then the rest, each group in path order.

Every bar is the same length, because these are findings rather than quantities. The label is the item name, the line under it is who holds which role, and the value on the right is the role.

Color Meaning
Red A Content Manager, Publisher or System Administrator assignment on an item with permissions of its own
Amber Any other assignment on an item with permissions of its own

Red bars also get an amber outline. When nothing breaks inheritance, the chart says so.

By person

One bar per principal, with its length set by how many assignments that principal holds. Principals holding any of the three powerful roles come first and are drawn amber with an outline; the rest are green. The line under the name lists the roles they hold. Hover for their assignment count, how many of those can change what other people see, and how many were granted on an item that does not inherit.

Both views draw as many rows as fit under the page’s chart height limit, and the note under the chart says when there are more.

Reading the grid

The SSRS permissions grid
One row per role assignment, on the item where it was set, with what the role allows and how many items it reaches.
Column What it holds
Item The catalog path the assignment was set on, / for the root folder, or (site wide) for a system role
Who The user or group name
Role The role assigned
What that allows A short description of the role, for the built in roles
Scope system for a site wide role, root for the root folder, own permissions for an item that no longer inherits from its folder
Items How many catalog items the assignment reaches: the item it was set on and everything that inherits from it. Blank for a system role
Authentication Windows, or Not Windows with the report server’s own type number
Notes What the page found about this assignment, in words

Role and Scope are drawn in red or amber on an assignment on an item with permissions of its own.

What that allows reads:

Role Description
Content Manager full control, including permissions
Publisher can publish and overwrite reports
Browser can view and subscribe for themselves
Report Builder can open report definitions
My Reports full control of their own folder
System Administrator site settings and role definitions
System User can see site settings

Each assignment appears once. An item that inherits is not listed on its own; it is counted in the Items column of the assignment it inherits, so the root folder’s assignments carry the size of everything that has not broken inheritance.

Findings

The headline counts the role assignments (each one a policy, a principal and a role, counted once however many items inherit it), the principals, and how many items break inheritance (or says everything inherits from its folder). The line under it adds, where they apply:

  • How many powerful role assignments are on items with permissions of their own, which is where a permission nobody remembers setting usually turns out to be.
  • How many assignments carry Content Manager, Publisher or System Administrator.
  • How many principals hold System Administrator on the site itself.
  • How many principals have no assignments, no content and no subscriptions, with the first three names.
  • How many assignments are held by a principal authenticated by something other than Windows.
  • Always: that these are assignments rather than effective permissions.
  • That the page reads the catalog tables directly, so it shows every assignment on the server rather than what the portal would show the person reading it.

Actions

  • Click a bar in By item to select that assignment’s row in the grid.
  • Click a bar in By person to select the first grid row for that principal.
  • Double click a bar in By item to open that item in SSRS Catalog Inventory with its row selected. In By person a double click selects the principal’s first row.
  • Right click a bar for the same menu as that bar’s grid row (in By person, the principal’s first row).
  • Right click a grid row, below the usual Copy, Copy with Headers and Select All, for:
    • Copy the item path (not offered on a site wide role)
    • Copy the principal name
    • Copy everything name can reach – every assignment that principal holds, one per line, tab separated: the item path, the role, the scope and how many items it reaches, or (site wide) and the role for a system role
    • This item in the catalog – opens SSRS Catalog Inventory with this item selected. On a site wide role or the root folder, where there is no item path to land on, it reads Open Catalog Inventory instead
    • The server settings – opens SSRS Server Configuration
  • Right click an empty part of the chart to copy the chart to the clipboard.

Nothing on this page grants or revokes anything. The security tables have to stay consistent with each other and with a policy the report server service caches, and the web portal changes them correctly. This page is for finding the item to go and fix.

Where the data comes from

Two result sets in one batch.

The assignments. dbo.PolicyUserRole joined to dbo.Policies, dbo.Users and dbo.Roles, one row per assignment (PolicyUserRole holds each policy, user and role combination once). A policy is shared by every item that inherits it, so each policy is shown on one item, the one with Catalog.PolicyRoot set, which is where somebody set the permissions, together with a count of the items carrying that policy. An item with no ParentID is the root folder. The system policy (Policies.PolicyFlag = 1), which holds System Administrator and System User, is attached to no catalog item and is shown as system scope.

Authentication. Users.AuthType 1 is Windows: the catalog’s own procedures (GetUserID and GetPrincipalID) look a principal up by SID when it is 1. Any other value is shown as Not Windows with its number.

The principals with nothing. dbo.Users rows with no PolicyUserRole row, no catalog item they created or last modified, and no subscription they own or last modified. A report server adds a user row the first time it sees somebody and never removes one.

Frequently asked questions

Why does the grid have fewer rows than there are items? An assignment is listed once, on the item where it was set. The items that inherit it are counted in its Items column rather than repeated, so a folder with ten reports under it and three assignments is three rows, each reaching eleven items.

Does this show what a particular person can open? Not exactly. It shows what is assigned to each user or group name. A person can reach an item through any Windows group they belong to, and the report server database does not record group membership.

Why is the root folder never flagged? The root’s policy is the server wide starting point. A Content Manager assignment there is what every report server has, so flagging it would flag the administrator on every server.