SQL Server Snippets | Paul Hewson

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

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'