Is it possible via a query, specific to 'SSRS' (SQL Server Reporting Services) to get an inventory of all reports on the server, e.g. name, folder, creation date, and which users have permissions to run/view those reports, this is for MSSQL SSRS in version 2016. This is a new task but as some of the reports are drawing sensitive records from the underlying data sources, we need assurance that access is appropriately restricted, and an inventory and corresponding access list would be an excellent start point.
This of course (possibly incorrectly) assumes all SSRS report/report folder access permissions settings is actually stored in a system databases, and the software doesnt integrate with the local OS security groups or indeed even domain groups for controlling access to SSRS reports. My guess is the permissions info will still be stored within a particular system database table(s), and access control entries will be a combination of MSSQL authentication, local OS group members, and domain security groups.
It would also be interesting to learn how often these reports are being accessed, to identify any unused stale reports that no longer serve a purpose to the organisation.
May 18, 2022 at 8:10 am
Look at the following tables in the reportserver DB and these should give you the information you need:
Execution log - Tells you who had run what. (this is defaulted to the last 60days)
Catalog - Should give you some of the information you are after.
If you want to join them use itemid in the catalog and reportid in execution log.
Also just found this online
select u.UserName, r.RoleName, r.Description, c.Path, c.Name
from dbo.PolicyUserRole pur
inner join dbo.Users u on pur.UserID = u.UserID
inner join dbo.Roles r on pur.RoleID = r.RoleID
inner join dbo.Catalog c on pur.PolicyID = c.PolicyID
order by u.UserName
Think this is more along the lines of what you are after.
May 18, 2022 at 8:19 am
Thank you for the pointers, much appreciated.
Out of interest, if you have administrated an SSRS server before, the reports seem to be neatly grouped within folders, therefore is it common to apply permissions at the folder level, or item (report) level. I was trying to draw comparisons to file servers, where normally you set permissions at the root folder and they inherit down, rather than at an item (document) level.
May 18, 2022 at 8:22 am
can you share where you got the query from, the tables look a good starting point.
May 18, 2022 at 8:52 am
In answer to the question:
Out of interest, if you have administrated an SSRS server before, the reports seem to be neatly grouped within folders, therefore is it common to apply permissions at the folder level, or item (report) level. I was trying to draw comparisons to file servers, where normally you set permissions at the root folder and they inherit down, rather than at an item (document) level.
Permission on folder in SSRS should always be managed my active directory groups, there maybe cases where if you have a folder on a team basis then you may want to give a certain report user based access but i would recommend this is a rareity.
Got the information on the last query from
Other stuff i googled querying report server there are loads of really useful scripts out there.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply