Configuring a new SQL Reporting Services server

I am now getting into my stride on upgrading to SQL 2008. We have a reasonably compact environment here with only a handful of production servers. We use mirroring and replication in places and we have a couple of report servers to keep the data flowing to the users. I have just moved a database that has a gang of reports pointing to it and have been going through the directory settings for security and so forth and got to the point where I was repeatedly selecting an object (data sources or folders mostly) and going to the properties and selecting "hidden" and applying the change. That got tedious really quickly so I moved over to SSMS and started reviewing the Catalog table in the ReportServer database. 

Now lets get things clear from the start, mucking about in system generated objects on a working server can easily result in a big mess around your ankles very quickly so only go here if you are totally happy that you can undo the changes you have made if you get something wrong.

Right, still with me? Great. First of all we need to get to the database and find some information about the objects we want to work with.

USE [ReportServer]
go

SELECT top 20 

    [c].[Path] ,
    [c].[Name] ,
    [c].[Type] ,
    [c].[Description] ,
    [c].[Hidden]
FROM    [dbo].[Catalog] AS c

so this shows we have a column called hidden that we can switch to a value of 1 to hide the objects we want and objects have a type value. Now some research on my 2005 server tells me that there are 6 types that I need to deal with and they match up to objects as:

--    1-directory
--    2-report
--    3-other file
--    4-linkedreport
--    5-data source
--    6-reportbuilder object

So, in my case, I want to hide all images and text files as they only appear in reports, noone goes to a report server to view a picture or a text file and can use

UPDATE  [dbo].[Catalog]
SET     [Hidden] = 1
WHERE   [Hidden] != 1
        AND [Type] = 3 -- images

Now, I also want to hide any directories called "Data Sources" as I dont want these in the way of users navigating the report directories and I can do that with

UPDATE  [dbo].[Catalog]
SET     [Hidden] = 1
WHERE   [Hidden] != 1
        AND ( [Type] = 1 -- directories
              AND [dbo].[Catalog].[Name] LIKE 'Data%Sources' -- directories with specified name(s)
            ) 

Now I had to use a LIKE and add a % in the name as some of our projects got created with a typo in the deployment properties and the names are either "DataSources" or "Data Sources"; you may not need this or you may need to add other code to include all the variations you have.

So there you have it, the whole report server deployment gets configured pretty much instantly. Just remember to run this once in a while as other projects come online so that nothing sneaks past you. Maybe even slip the code into your scheduled weekly maintenance job(s).


[Edit - 27/Mar/2010]
I am adding the following section after receiving some messages (via http://twitter.com/fatherjack) that this technique is inappropriate.


For clarity that may be missing from the above, this is not a method to apply any form of security over a report server and its contents, this is simply to hide items that otherwise clog up the view of a user browsing the report server. There is, to my knowledge, no option in setting permissions on a report server object that allows the user to still access the item but not be able to see it. I apply the hidden property on items that the user inherently HAS permission to consume - images may be used in reports they have full access to, the data sources are obviously needed for the reports to run properly. There is no need for the user to have to navigate past these items, so I hide them.
Permissions are used to control access to data and strongly tie in with Active Directory users and groups and SQL Server Logins and Users, this ensures that no person has access to a report that contains data they are not authorised to view.


Please be sure to read the code disclaimer page before you use any code in this blog.