Just as I found when I arrived in the school district, Dominican has made good use of SSRS self-service reporting before I got there.  The difference is that, this time, instead of hundreds of hand-me-down reports, I found over a thousand.

Since a lot of my job is going to involve moving some databases around, I had to figure out where the dependencies on these databases might be buried — and that included embedded SQL queries in a thousand production RDLs.

The first step was obviously to document them and figure out what reports used what databases and tables.  I whipped out  xmlRSDocs, which includes a handy-dandy report and some fancy parsing of RDL command texts to handle just this requirement, and got to work.

Everything was just perfect, until I set up an SSIS package to update XMLRSDocs’ cached RDL information regularly.

Whoops

While I could run the DTSX locally, with my DBA privileges, the identity that needed to run the package on a schedule couldn’t run it.  Why not?

XMLRSDocs trolls through all the SSRS artifacts recursively, using the SSRS Web Service API, reads in every RDL and parses it into a useable form for extrapolating various kinds of information.  But the agent identity didn’t have rights to every single RDL; various SSRS folders have been assigned to various departments and access has been appropriately limited to them.

You might ask, “why not just assign the agent identity to an appropriate domain group, so that it will have rights to all the RDLs?”  Sigh.  In the past, IT folks seem to have done this by using the built in Administrators group.  There is no way I’m giving this identity membership in that group.

Obviously, I’ll be getting such a group set up, but the task of finding out what folders, data sources, reports, and other SSRS catalog types (hold that thought) have been explicitly needed so that the group needs to be assigned privileges to them, still remains.

How can we do it?

It’s all very well to say “we’ll use XMLRSDocs to find out”, but that’s just it.  XMLRSDocs can’t find out anything about items to which it doesn’t have permissions.

In fact, I never worried much about how to document SSRS permissions before and never bothered to look at how I would do that.

We’re going low-level

It’s also all very well for the SSRS team — much as I respect them — to say that they don’t document the Reporting Services base tables.  Somebody I respect incredibly highly, in fact, has stated that we shouldn’t even think about catalog types from a database level; we should always be getting all this information from the SSRS web service API.

That’s nice, but when the problem is that my identity doesn’t have rights to look at various objects I can’t find out about their permissions.  I’m sure this is by design, but sometimes you’ve got to do what you’ve got to do.

So I buckled down and took a look at some tables in the SSRS db that I’d never paid attention to, before.

It didn’t turn out to be too bad — and actually you may find this useful, too — so, here you go.  The relationships were not nearly as wacky and odd as they could have been.

if @HasPermissions = 1
   or replace(ltrim(rtrim(@UserName)), '(any)', space(0)) = space(0)

-- we're going to look at the permissions that somebody, or everyone, has

begin

   select distinct cat.Path
      ,cat.name
      ,cat.Type as Type
      ,u.UserName
      ,case 
         when @IncludeRoles = 0
            then space(0)
         else r.RoleName
         end as RoleName
   from catalog as cat
   inner join PolicyUserRole as pur on cat.PolicyID = pur.PolicyID
   inner join Users as u on pur.UserID = u.UserID
   inner join Roles as r on pur.RoleID = r.RoleID
   where (
         (@UserName = '(any)')
         or (u.UserName like '%' + LTRIM(RTRIM(@UserName)) + '%')
         )
      and (cat.Type in (@CatalogTypes))
      and (
         (@CatalogItemPath = '(any)')
         or (cat.Path + cat.name like '%' + LTRIM(RTRIM(@CatalogItemPath)) + '%')
         )
   order by cat.Path
      ,cat.name
      ,RoleName
      ,u.UserName
end
else
begin

-- we're going to look at the permissions that somebody lacks

   select distinct cat.Path
      ,cat.name
      ,cat.Type as Type
      ,null as UserName
      ,null as RoleName
   from catalog cat
   where (cat.Type in (@CatalogTypes))
      and (
         (@CatalogItemPath = '(any)')
         or (cat.Path + cat.name like '%' + LTRIM(RTRIM(@CatalogItemPath)) + '%')
         )
      and cat.ItemID not in (
         select cat.ItemID
         from catalog as cat
         inner join PolicyUserRole as pur on cat.PolicyID = pur.PolicyID
         inner join Users as u on pur.UserID = u.UserID
         where (u.UserName like '%' + LTRIM(RTRIM(@UserName)) + '%')
         )
   order by cat.Path
      ,cat.name 

end 

We’re going to run a report

Big surprise, it’s easier to analyze this stuff when you can see it, so I tarted up the query you see above with a couple of nice parameters.  You can ignore them if you’re not interested in the same exact problem that I was, but basically I wanted to be able to see all objects to which somebody did, or did not, have access to, with the first usage being which objects the agent identity could not “see”.

What’s so important about catalog types?

I’m figuring I’m going to run this report, or these queries, first for folder type SSRS artifacts only.  If we assign privileges to the new group just to those folders, chances are it will fix 90% of the objects of any type housed within them, which will inherit.  Then I can run the report again for all the other types and quickly clean up the stragglers.

So, FWIW, the report has a second dataset for the picklist, which I also use in the report body via the Lookup function I talked about last time:

Open Sesame…  

What do you reckon?

I’m thinking that’s so crazy it just might work as some other people I respect very highly, and who are not on the reporting team, have always said.

Hmm.  I hope HBO doesn’t make any changes to that particular ethos.