{"id":33,"date":"2015-08-16T11:17:00","date_gmt":"2015-08-16T18:17:00","guid":{"rendered":"\/lisa\/post\/2015\/08\/16\/Documentation-requires-rights-in-SSRS-but-who-has-them.aspx"},"modified":"2021-11-24T11:20:07","modified_gmt":"2021-11-24T19:20:07","slug":"documentation-requires-rights-in-ssrs-but-who-has-them","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2015\/08\/16\/documentation-requires-rights-in-ssrs-but-who-has-them\/","title":{"rendered":"Documentation requires rights in SSRS – but who has them?"},"content":{"rendered":"

Just as I found when I arrived in the school district, Dominican<\/a> <\/span>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<\/a>, I found over a thousand.<\/p>\n

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.<\/p>\n

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

Everything was just perfect, until I set up an SSIS package to update XMLRSDocs’ cached RDL information regularly.<\/p>\n

Whoops<\/h4>\n

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?<\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

How can we do it?<\/p>\n

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.<\/p>\n

In fact, I never worried much about how to document SSRS permissions before and never bothered to look at how I would do that.<\/p>\n

We’re going low-level<\/h4>\n

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<\/a>, 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.<\/p>\n

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.<\/p>\n

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

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.<\/p>\n

if<\/span> @HasPermissions =<\/span> 1\n   or<\/span> replace<\/span>(<\/span>ltrim<\/span>(<\/span>rtrim<\/span>(<\/span>@UserName)<\/span>)<\/span>,<\/span> '(any)'<\/span>,<\/span> space<\/span>(<\/span>0)<\/span>)<\/span> =<\/span> space<\/span>(<\/span>0)<\/span><\/pre>\n
-- we're going to look at the permissions that somebody, or everyone, has<\/span><\/pre>\n
\n
begin<\/span><\/pre>\n<\/div>\n
   select<\/span> distinct<\/span> cat.<\/span>Path\n      ,<\/span>cat.<\/span>name<\/span>\n      ,<\/span>cat.<\/span>Type as<\/span> Type\n      ,<\/span>u.<\/span>UserName\n      ,<\/span>case<\/span> \n         when<\/span> @IncludeRoles =<\/span> 0\n            then<\/span> space<\/span>(<\/span>0)<\/span>\n         else<\/span> r.<\/span>RoleName\n         end<\/span> as<\/span> RoleName\n   from<\/span> catalog<\/span> as<\/span> cat\n   inner join<\/span> PolicyUserRole as<\/span> pur on<\/span> cat.<\/span>PolicyID =<\/span> pur.<\/span>PolicyID\n   inner join<\/span> Users as<\/span> u on<\/span> pur.<\/span>UserID =<\/span> u.<\/span>UserID\n   inner join<\/span> Roles as<\/span> r on<\/span> pur.<\/span>RoleID =<\/span> r.<\/span>RoleID\n   where<\/span> (<\/span>\n         (<\/span>@UserName =<\/span> '(any)'<\/span>)<\/span>\n         or<\/span> (<\/span>u.<\/span>UserName like<\/span> '%'<\/span> +<\/span> LTRIM<\/span>(<\/span>RTRIM<\/span>(<\/span>@UserName)<\/span>)<\/span> +<\/span> '%'<\/span>)<\/span>\n         )<\/span>\n      and<\/span> (<\/span>cat.<\/span>Type in<\/span> (<\/span>@CatalogTypes)<\/span>)<\/span>\n      and<\/span> (<\/span>\n         (<\/span>@CatalogItemPath =<\/span> '(any)'<\/span>)<\/span>\n         or<\/span> (<\/span>cat.<\/span>Path +<\/span> cat.<\/span>name<\/span> like<\/span> '%'<\/span> +<\/span> LTRIM<\/span>(<\/span>RTRIM<\/span>(<\/span>@CatalogItemPath)<\/span>)<\/span> +<\/span> '%'<\/span>)<\/span>\n         )<\/span>\n   order<\/span> by<\/span> cat.<\/span>Path\n      ,<\/span>cat.<\/span>name<\/span>\n      ,<\/span>RoleName\n      ,<\/span>u.<\/span>UserName\nend<\/span>\nelse<\/span>\nbegin<\/span><\/pre>\n
-- we're going to look at the permissions that somebody lacks<\/span><\/pre>\n
   select<\/span> distinct<\/span> cat.<\/span>Path\n      ,<\/span>cat.<\/span>name<\/span>\n      ,<\/span>cat.<\/span>Type as<\/span> Type\n      ,<\/span>null<\/span> as<\/span> UserName\n      ,<\/span>null<\/span> as<\/span> RoleName\n   from<\/span> catalog<\/span> cat\n   where<\/span> (<\/span>cat.<\/span>Type in<\/span> (<\/span>@CatalogTypes)<\/span>)<\/span>\n      and<\/span> (<\/span>\n         (<\/span>@CatalogItemPath =<\/span> '(any)'<\/span>)<\/span>\n         or<\/span> (<\/span>cat.<\/span>Path +<\/span> cat.<\/span>name<\/span> like<\/span> '%'<\/span> +<\/span> LTRIM<\/span>(<\/span>RTRIM<\/span>(<\/span>@CatalogItemPath)<\/span>)<\/span> +<\/span> '%'<\/span>)<\/span>\n         )<\/span>\n      and<\/span> cat.<\/span>ItemID not<\/span> in<\/span> (<\/span>\n         select<\/span> cat.<\/span>ItemID\n         from<\/span> catalog<\/span> as<\/span> cat\n         inner join<\/span> PolicyUserRole as<\/span> pur on<\/span> cat.<\/span>PolicyID =<\/span> pur.<\/span>PolicyID\n         inner join<\/span> Users as<\/span> u on<\/span> pur.<\/span>UserID =<\/span> u.<\/span>UserID\n         where<\/span> (<\/span>u.<\/span>UserName like<\/span> '%'<\/span> +<\/span> LTRIM<\/span>(<\/span>RTRIM<\/span>(<\/span>@UserName)<\/span>)<\/span> +<\/span> '%'<\/span>)<\/span>\n         )<\/span>\n   order<\/span> by<\/span> cat.<\/span>Path\n      ,<\/span>cat.<\/span>name<\/span> <\/pre>\n
end<\/span> <\/p>\n
We’re going to run a report<\/h4>\n
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”.<\/p>\n
\"SSRS<\/p>\n
What’s so important about catalog types?<\/h4>\n
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.<\/p>\n
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<\/a>:<\/p>\n
SELECT 1 AS Value, ‘Folder’ AS Name UNION 
SELECT 2 , ‘Report’ UNION 
SELECT 3 , ‘Resource’ UNION 
SELECT 4 , ‘Linked Report’ UNION 
SELECT 5 , ‘Data Source’ UNION 
SELECT 6 , ‘Report Model’ UNION 
SELECT 9 , ‘Report Part’ UNION 
SELECT 8 , ‘Shared Dataset’ UNION 
SELECT NULL , ‘System’<\/div>\n
Open Sesame…  <\/h4>\n
What do you reckon?<\/p>\n
I’m thinking that’s so crazy it just might work <\/em><\/strong>as some other people I respect very highly<\/a>, and who are not on the reporting team, have always said.<\/p>\n
Hmm.  I hope HBO<\/a> doesn’t make any changes to that particular ethos.<\/p>\n","protected":false},"excerpt":{"rendered":"
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, IRead more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6],"tags":[],"class_list":["post-33","post","type-post","status-publish","format-standard","hentry","category-reporting","category-sql-server"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/33","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=33"}],"version-history":[{"count":1,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/33\/revisions"}],"predecessor-version":[{"id":495,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/33\/revisions\/495"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=33"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=33"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=33"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}