December 1, 2014 at 5:35 pm
Comments posted to this topic are about the item Investigate Report Server Security
December 15, 2014 at 11:42 am
Excellent script!
I have made a change to the script so that I don't have to be in the ReportServer database to execute.
INNER JOIN reportserver.dbo.Policies Pol ON Cat.PolicyID = Pol.PolicyID
INNER JOIN reportserver.dbo.PolicyUserRole PUR ON Pol.PolicyID = PUR.PolicyID
INNER JOIN reportserver.dbo.Users Us ON PUR.UserID = Us.UserID
INNER JOIN reportserver.dbo.Roles Rol ON PUR.RoleID = Rol.RoleID
Rudy
December 15, 2014 at 2:08 pm
There would appear to be an error in CASE statement as there are two when 3's ?
December 17, 2014 at 10:10 am
it's a very simple script but it helps a lot
December 17, 2014 at 10:11 am
thanks for the correction
it would be
CASE Cat.Type WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'Resource'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source' ELSE '' END AS CatalogType
December 17, 2014 at 11:16 am
That is what I thought and used though I use "?" instead of "" for unknown. By the way do you know what a type = 8 would be? I have values of 8 in catalog but it is not part of the join result set but since I do not know what an 8 is I would like to identify it if possible.
-- Mark D Powell --
December 17, 2014 at 12:02 pm
i did some research
and this is what I found
WHEN C.type = 1 THEN '1-Folder'
WHEN C.type = 2 THEN '2-Report'
WHEN C.type = 3 THEN '3-File'
WHEN C.type = 4 THEN '4-Linked Report'
WHEN C.type = 5 THEN '5-Datasource'
WHEN C.type = 6 THEN '6-Model'
WHEN C.type = 7 Then '7-ReportPart'
WHEN C.type = 8 Then '8-Shared Dataset'
December 17, 2014 at 1:15 pm
Thanks. I had tried a search in my downloaded version of Books Online but I just found a slew of hits for general articles on the system views and Full Text catalogs. Do you have a link or can you identify the correct manual to look for?
December 17, 2014 at 1:33 pm
Here is how I update the script
USE ReportServer
GO
DECLARE @UserName VARCHAR(200) = '%%' -- If you want to list all users
SET @UserName = '%BUILTIN\Administrators%' -- If you want to list a single user
SELECT Rol.RoleName,Us.UserName ,Cat.PATH,Cat.[Name] ReportName,
CASE Cat.Type
WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'File'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Datasource'
WHEN 6 THEN 'Model'
WHEN 7 Then 'ReportPart'
WHEN 8 Then 'Shared Dataset'
END AS CatalogType
,Cat.Description
FROM Catalog Cat
INNER JOIN reportserver.dbo.Policies Pol ON Cat.PolicyID = Pol.PolicyID
INNER JOIN reportserver.dbo.PolicyUserRole PUR ON Pol.PolicyID = PUR.PolicyID
INNER JOIN reportserver.dbo.Users Us ON PUR.UserID = Us.UserID
INNER JOIN reportserver.dbo.Roles Rol ON PUR.RoleID = Rol.RoleID
WHERE Cat.Type in (1,2)
AND ( Us.UserName LIKE @UserName )
ORDER BY Cat.PATH
Rudy
September 7, 2016 at 6:47 am
Rudy Panigas (12/17/2014)
Here is how I update the scriptUSE ReportServer
GO
DECLARE @UserName VARCHAR(200) = '%%' -- If you want to list all users
SET @UserName = '%BUILTIN\Administrators%' -- If you want to list a single user
SELECT Rol.RoleName,Us.UserName ,Cat.PATH,Cat.[Name] ReportName,
CASE Cat.Type
WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'File'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Datasource'
WHEN 6 THEN 'Model'
WHEN 7 Then 'ReportPart'
WHEN 8 Then 'Shared Dataset'
END AS CatalogType
,Cat.Description
FROM Catalog Cat
INNER JOIN reportserver.dbo.Policies Pol ON Cat.PolicyID = Pol.PolicyID
INNER JOIN reportserver.dbo.PolicyUserRole PUR ON Pol.PolicyID = PUR.PolicyID
INNER JOIN reportserver.dbo.Users Us ON PUR.UserID = Us.UserID
INNER JOIN reportserver.dbo.Roles Rol ON PUR.RoleID = Rol.RoleID
WHERE Cat.Type in (1,2)
AND ( Us.UserName LIKE @UserName )
ORDER BY Cat.PATH
Thanks for the updates.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply