Delen via

Sample Script for Setting Item-level Security in Reporting Services

  • Artikel

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 https://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

  • Anonymous
    March 17, 2004
    [SQL Server]Sample Script for Setting Item-level Security in Reporting Services
  • Anonymous
    March 18, 2004
    You have been Taken Out! Comments about your posting in this link. Thanks!
  • Anonymous
    April 21, 2004
    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.
  • Anonymous
    April 21, 2004
    Hi RS User,

    The script is designed to add in the inherited permissions. Make sure you set keepCurrentPolicy to true
  • Anonymous
    April 23, 2004
    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
  • Anonymous
    May 18, 2004
    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
  • Anonymous
    May 18, 2004
    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
  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/2185222-creating-a-user-programmatically
  • Anonymous
    May 29, 2009
    PingBack from http://paidsurveyshub.info/story.php?title=bryan-s-blog-sample-script-for-setting-item-level-security-in
  • Anonymous
    June 08, 2009
    PingBack from http://quickdietsite.info/story.php?id=801
  • Anonymous
    June 19, 2009
    PingBack from http://mydebtconsolidator.info/story.php?id=5315