Find Reporting Services permissions.

[ptheriault]

I have put together a query with the help of fuzzyocelot that will report permissions that users have access to in Reporting Services. I thought some of you may find this useful.

SELECT DISTINCT 
       CASE c.[Type] 
            WHEN 5 THEN 'DataSource'
            WHEN 6 THEN 'Models'
            WHEN 1 THEN 'Folder'
            WHEN 4 THEN 'LinkedReport' 
            WHEN 2 THEN 'Report'
            WHEN 3 THEN 'Resource'
            WHEN 0 THEN 'Unknown'
        END AS [Type], 
        c.[Name] as object,
        U.UserName
FROM    dbo.[Catalog] C 
INNER JOIN dbo.Policies P 
   ON C.PolicyID = P.PolicyID 
INNER JOIN dbo.PolicyUserRole PUR 
   ON P.PolicyID = PUR.PolicyID 
INNER JOIN dbo.Users U 
   ON PUR.UserID = U.UserID
WHERE c.[Name] NOT IN ('')
  AND C.Path NOT LIKE N'/Users Folders%' 
ORDER BY Type, object

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[onpnt]

Good tip Paul

_{____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[fuzzyocelot]

That's a great query! Thanks, Paul!