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 USEReportServer;
SELECTCASE WHEN (SubString(c.[Path], 1,Len(c.[Path])-(CharIndex('/',Reverse(c.[Path]))- 1)))='' THEN'<root folder>' ELSESubString(c.[Path], 1,Len(c.[Path])-(CharIndex('/',Reverse(c.[Path]))- 1)) ENDASFolder ,C.Name ,s.Description ,s.LastRunTime ,REPLACE(REPLACE(CAST(CAST(extensionsettingsASXML).query('/ParameterValues/ParameterValue/Value[../Name = ''TO'']')ASVARCHAR(MAX)),'</Value>',''),'<Value>','')AS'To:' ,REPLACE(REPLACE(CAST(CAST(extensionsettingsASXML).query('/ParameterValues/ParameterValue/Value[../Name = ''CC'']')ASVARCHAR(MAX)),'</Value>',''),'<Value>','')AS'CC:' ,REPL…

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.

USEReportServer;
SELECT CASE WHEN (SubString(c.[Path], 1,Len(c.[Path])-(CharIndex('/',Reverse(c.[Path]))- 1)))='' THEN'<root folder>' ELSESubString(c.[Path], 1,Len(c.[Path])-(CharIndex('/',Reverse(c.[Path]))- 1)) ENDASFolder ,CASE WHENc.Type= 1 THEN'Path' WHENc.Type= 2 THEN'Report' WHENc.Type= 3 THEN'Resource' WHENc.Type= 4 THEN'Linked Report' WHENc.Type= 5 THEN'Data Source' WHENc.Type= 6 THEN'Report Model' WHENc.Type= 7 THEN'Report Part' WHENc