Chris Umbel

SSRS scripting with RS.EXE

At work we've recently deployed SQL Server Reporting Services (SSRS) and it's been quite interesting. In general 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 this reference to see what all is exposed to you.

Happy SSRS administration!

Tue Nov 20 2007 00:11:00 GMT+0000 (UTC)

Follow Chris
RSS Feed
Twitter
Facebook
CodePlex
github
LinkedIn
Google