Skip to main content

Posts

Showing posts from March, 2014

Show all SSRS reports and their subscriptions

This is a very handy script to give you a quick overview of the report subscriptions you have on your Microsoft SQL Server Reporting Services server. It works on SSRS 2005 to 2012. -- Show reports and their subscriptions USE ReportServer ; SELECT CASE               WHEN ( SubString ( c . [Path] , 1 , Len ( c . [Path] ) - ( CharIndex ( '/' , Reverse ( c . [Path] )) - 1 ))) = ''                      THEN '<root folder>'               ELSE SubString ( c . [Path] , 1 , Len ( c . [Path] ) - ( CharIndex ( '/' , Reverse ( c . [Path] )) - 1 ))               END AS Folder        , C . Name        , s . Description        , s . LastRunTime        , REPLACE ( REPLACE ( CAST ( CAST ( extensionsettings AS XML ). query ( '/ParameterValues/ParameterValue/Value[../Name = ''TO'']' ) AS VARCHAR ( MAX )), '</Value>' , '' ), '<Value>' , '' ) AS

Show permissions on all SSRS folders

Here is a useful (if rather verbose) script that will show users and their permissions from the root folder on SQL Server Reporting Services downwards. It should work on most if not all versions of the product. You can use it as is, or as a starting point to create your own aggregations etc. USE ReportServer ; SELECT CASE               WHEN ( SubString ( c . [Path] , 1 , Len ( c . [Path] ) - ( CharIndex ( '/' , Reverse ( c . [Path] )) - 1 ))) = ''                      THEN '<root folder>'               ELSE SubString ( c . [Path] , 1 , Len ( c . [Path] ) - ( CharIndex ( '/' , Reverse ( c . [Path] )) - 1 ))               END AS Folder        , CASE               WHEN c . Type = 1                      THEN 'Path'               WHEN c . Type = 2                      THEN 'Report'               WHEN c . Type = 3                      THEN 'Resource'