Any scripts available to quickly generate Report about SSRS reports/permissions/location on server


Can some one point me to the right direction of finding a script which generates a report which includes all reporting services reports and the permissions they use along with their location on the server.

Thanks in advance, S

more ▼

asked Jun 08, 2010 at 10:44 AM in Default

avatar image

1.3k 75 104 147

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Report data is stored in the table named dbo.Catalog. Astonishingly, the Contents column which stores the RDL information has image data type (the very data type which Microsoft strongly encourages us to avoid). The workaround is to cast contents as XML friendly varbinary(max) which can then be cast to xml data type. Here is the script which will list all reports and their respective parameters, so if you run it you will get multiple records per report if the latter has multiple parameters:

    [name] ReportName, [path] ReportPath,
    item.value('@Name', 'varchar(100)') as ParameterName,
    item.value('DataType[1]', 'varchar(100)') as DataType,
    isnull(item.value('AllowBlank[1]', 'varchar(5)'), 'false') as AllowBlank,
    isnull(item.value('Prompt[1]', 'varchar(100)'), '') as Prompt,
    isnull(item.value('Hidden[1]', 'varchar(100)'), 'false') as Hidden,
    item.value('data(DefaultValue/Values/Value)[1]', 'varchar(100)') as ParameterValue
            [Path], [Name], cast(cast([Content] as varbinary(max)) as xml) as ReportXml 
            from dbo.Catalog 
            where [Content] is not null and [Type] = 2
    ) Reports 
    cross apply ReportXml.nodes('/Report/ReportParameters/ReportParameter') items(item)
    order by 1;

All tables in the ReportServer database are very straightforward, i.e. dbo.ReportSchedule relates to dbo.Schedule and dbo.Catalog via ScheduleID - > ScheduleID and ReportID -> ItemID respectively.


more ▼

answered Jun 08, 2010 at 02:11 PM

avatar image

20.3k 3 7 29

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 08, 2010 at 10:44 AM

Seen: 2893 times

Last Updated: Jun 08, 2010 at 10:44 AM

Copyright 2018 Redgate Software. Privacy Policy