Sample Script for Setting Item-level Security in Reporting Services


Report Manager is a solid, Web-based administrative tool for Reporting Services, but it is not the end-all-be-all of administrative functionality for a report server. There are many capabilities available through the Web service that Report Manager does not take advantage of. Case in point: Setting a security policy (otherwise known as a role assignment) for an item using Report Manager is a bit cumbersome, especially if the item for which you want to add a policy is nested several folders deep in your report server namespace. Let's take an example. Let's say you have a folder at “/Budgets/Finance” and you would like to give Bob permission to view reports in the Finance folder. You add a new role assignment for Bob and make him a Browser user of the Finance folder. Bob logs into Report Manager hoping to navigate to the Finance folder, but...Bob is not able to see any folders when he logs in. What gives? Well, unfortunately the path to Bob's Finance folder actually contains three items: The Home or root folder (”/”), the Budgets folder and the Finance folder. Because Bob is not allowed to view the root folder or the Budgets folder, he will never see the Finance folder for which he has permissions. Sorry Bob. In order to give Bob Browser permissions on the Finance folder, you must add Bob as a Browser of both the Home and Budgets folders. If you need to add more groups or users to the Finance folder, it can get downright tiresome. Oh, did I mention that every time you add a policy for Bob to one of the folders, the inherited policy is broken. Yes, that means that any users that enjoyed inherited permissions to those items (for example BUILTIN\Administrators or some other administrator group) no longer have any access rights. You will have to re-add inherited permissions as local policies on each of the three folders in our example. Yikes. Fortunately for Bob, Reporting Services offers rs.exe, a scripting utility with complete access to the Reporting Services Web service. You can use this scripting tool to automate certain tasks and to perform administrative functions that may not be easily automated through Report Manager.


The following sample script can be used to add a security policy for a nested folder or report which automatically gives the user permissions up the namespace tree. After using this script, the item is immediately accessible to the user. In addition, you can use this script to keep or delete the current set of policies for the item, including inherited ones. I haven't given the script the whole battery of tests yet, so if you play around with the code, try it on your test server only. If you find any problems or issues, let me know.


 


'=====================================================================


'  File:     AddItemSecurity.rss


'


'  Summary:  Demonstrates a script that can be used with RS.exe to


'                set security on an item in Reporting Services.


'


'---------------------------------------------------------------------


' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY


' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE


' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A


' PARTICULAR PURPOSE.


'=====================================================================*/


'


' Variables that are passed on the command line with the -v switch:


' userName - the name of the user for which to add a policy


' roleName - the name of the role to apply for the user (i.e. Browser, Content Manager)


' itemPath - the path of the item for which you want to add the policy (i.e. /SampleReports)


' keepCurrentPolicy - whether to keep the current policy and add the new one


'


' Sample command line:


' rs -i AddItemSecurity.rss -s http://localhost/reportserver -v userName="MyTestUser"


'    -v roleName="Browser" -v itemPath="/SampleReports" -v keepCurrentPolicy="True"


 


Public Sub Main()


   Dim isRoot As Boolean = False


   Dim inheritParent As Boolean


   Dim policies() As Policy


   Dim newPolicies() As Policy


   Dim policy As New Policy()


   Dim roles(0) As Role


   roles(0) = New Role()


   roles(0).Name = roleName


   policy.Roles = roles


   policy.GroupUserName = userName


  


   While Not isRoot


      ' Once the root of the catalog is reached,


      ' stop applying policies


      If itemPath = "/" Then


         isRoot = True


      End If


      policies = rs.GetPolicies(itemPath, inheritParent)


        


      ' If the user selects not to keep inherited or current policy,


      ' empty the policy


      If Not keepCurrentPolicy = "True" Then


         policies = Nothing


      End If


      newPolicies = AddNewPolicy(policy, policies)


      rs.SetPolicies(itemPath, newPolicies)


      itemPath = GetParentPath(itemPath)


   End While


   Console.WriteLine("Policy successfully set.")


End Sub 'Main


  


 


' Method to parse the path of an item and retrieve


' the parent path of an item


Private Function GetParentPath(currentPath As String) As String


   Dim delimiter As String = "/"


   Dim rx As New System.Text.RegularExpressions.Regex(delimiter)


   Dim childPath As String() = rx.Split(currentPath)


  


   Dim parentLength As Integer = childPath.Length - 1


   Dim parentPath(parentLength) As String


  


   Dim i As Integer


   For i = 0 To parentLength - 1


      parentPath(i) = childPath(i)


   Next i


   If parentPath.Length = 1 Then


      Return "/"


   Else


      Return String.Join("/", parentPath)


   End If


End Function 'GetParentPath


  


' Takes the policy to add and applies it to the current set


' of policies if applicable


Private Function AddNewPolicy(policyToAdd As Policy, policies() As Policy) As Policy()


   If Not (policies Is Nothing) Then


      Dim policy As Policy


      For Each policy In  policies


         If policy.GroupUserName = policyToAdd.GroupUserName Then


            Throw New Exception("The supplied User policy already exists for the item.")


         End If


      Next policy


      Dim list As New System.Collections.ArrayList(policies)


      list.Add(policyToAdd)


      Return CType(list.ToArray(GetType(Policy)), Policy())


   Else


      policies = New Policy(0) {}


      policies(0) = policyToAdd


      Return policies


   End If


End Function 'AddNewPolicy

Comments (13)

  1. [SQL Server]Sample Script for Setting Item-level Security in Reporting Services

  2. You have been Taken Out! Comments about your posting in this link. Thanks!

  3. RS User says:

    The script is great. But I am loosing the inherited permissions other users enjoyed to the items for which I cange the policies. How can I programmatically re-add the inherited permissions as local policies on each of the items for which policies have been changed.

  4. Bryan Keller says:

    Hi RS User,

    The script is designed to add in the inherited permissions. Make sure you set keepCurrentPolicy to true

  5. RS User says:

    Hi Bryan,

    Thanks for the prompt reply. I was wondering what one should do to delete only a specified user policy for an item policy rather than deleting the whole policy for the item itself. Will just deleting the user from the users table in the RS database do or will we have to programmatically remove the user policy from the policies for a particular item.

    Thanks,

    RS User

  6. Jun says:

    How to do the item-level security in ASP.NET?

    I created a ASP.NET Web tools to add NT domain user name into a SQL Server user DB (Not the RS DB), I hope I could grant some item-level access privileges to those user at the same time, I notice there is a metod call SetPolicies exposed by the reporting web services, could I use that method to implement the function, could you show me some example, thanks in advance.

    Jun

  7. Jun says:

    How to do the item-level security in ASP.NET?

    I created a ASP.NET Web tools to add NT domain user name into a SQL Server user DB (Not the RS DB), I hope I could grant some item-level access privileges to those user at the same time, I notice there is a metod call SetPolicies exposed by the reporting web services, could I use that method to implement the function, could you show me some example, thanks in advance.

    Jun

Skip to main content