NOTE: This post was originally written in 2007 so it may be dated. I’m resurrecting it due to relative popularity.This post has been copied between several blogging systems (some of which were home-brewed) and some formatting has been lost along the way.

At work we’ve recently deployed SQL Server Reporting Services (SSRS) and it’s been quite interesting. In generaI, I love the product but of course as with everything there are situations where you have to do more than the management tools provide and you have to write some scripts.

SSRS provides an interesting scripting facility, RS.EXE, which allows you to run VB .Net code against the management web service of SSRS. I encountered a situation where I had to add a user to the policy as the “Browser” role for all folders in my SSRS environment. Because I didn’t want to inherit parent item security to all objects I had no clean way of accomplishing that other than programmatically. I wrote up a quick script and this task will be easy as pie from now on.

Here’s the basics of the code. It can just be slapped into the text editor of your choice. It’s just a script, not a proper VB app.

Public Sub Main()
  Dim items() as CatalogItem
  Dim item As CatalogItem
  Dim newPolicy as Policy
  Dim role as new Role()
  'returns EVERY CatalogItem in our reporting services instance
  items = rs.ListChildren("/", True)
  role.Name = "Browser"
  newPolicy = new Policy()
  ' the UserGroup variable will be passed in from the command line.
  ' see remarks later
  newPolicy.GroupUserName = UserGroup
  newPolicy.Roles = new Role() { role }
  Console.WriteLine("Adding Browser Role for user or group: " & _
  newPolicy.GroupUserName )
  For Each item In items
    ' Ensure we're only setting the policy on Folders and not User's
    ' special folders
    If item.Type = ItemTypeEnum.Folder And _
        Not item.Name.Equals("My Reports") And _
        Not item.Name.StartsWith("MYDOMAINNAME") Then
      SetNewPolicy(item.Path, newPolicy)
      Console.WriteLine(item.Path)
    End If
  Next
End Sub

Note that the CatalogItem, Policy and Role classes are all provided by the Reporting Services Web Services API for us to use. The rs object is a global instance of the ReportingServices class the engine provides us. Our Main() method here simply iterates all items we want to set permissions on and calls a SetNewPolicy routine which we have yet to write. The UserGroup global string object is going be passed in as an argument from the command line and will be demonstrated towards the end of this article.

Now we have to define our SetNewPolicy routine that will add our policy to a catalog item.

Private Sub SetNewPolicy(itemPath As String, newPolicy As Policy)
  Dim inherit as Boolean
  Dim existingPolicies() As Policy
  ' ask the web service for the current policies of the item in question
  existingPolicies = rs.GetPolicies(itemPath, inherit)
  If Not (existingPolicies Is Nothing) Then
    Dim policy As Policy
    Try
      For Each policy In existingPolicies
        If policy.GroupUserName = newPolicy.GroupUserName Then
          Throw New Exception("Policy Already exists, skipping")
        End If
      Next
      Dim policyList As New _
      System.Collections.ArrayList(existingPolicies)
      policyList.Add(newPolicy)
      ' instruct the web service to save our new policies
      rs.SetPolicies(itemPath, policyList.ToArray(GetType(Policy)))
      Catch Ex As Exception
      Console.WriteLine(Ex.Message)
    End Try
  End If
End Sub

The SetNewPolicy function basically grabs the existing policies of a specified item and adds our new policy to it assuming it doesn’t conflict with any existing policies via the rs.GetPolicies and rs.SetPolicies respectively.

Save this script as SetPermissions.rss and it can now be executed from the command line as such:

rs -i SetPermissions.rss -s http://MYSSRSBOX/reportserver -v UserGroup="MYDOMAINNAME\kilnaar"

See the ‘-v UserGroup=”MYDOMAINNAME\kilnaar”‘ argument? That’s what is fed into the global UserGroup variable used in our Main() function.

Pretty easy all in all. From here you should check out <a href=”http://msdn.microsoft.com/en-us/library/aa257520(SQL.80).aspx”>this reference</a> to see what all is exposed to you.

Happy SSRS administration!