Using the Reporting Services Web Service, how do y

Question

Using the SQL Server Reporting Services Web Service, how can I determine the permissions of a particular domain user for a particular report? The user in question is not the user that is accessing the Web Service.

I am accessing the Web Service using a domain service account (lets say MYDOMAIN\SSRSAdmin) that has full permissions in SSRS. I would like to programmatically find the permissions of a domain user (lets say MYDOMAIN\JimBob) for a particular report.

The GetPermissions() method on the Web Service will return a list of permissions that the current user has (MYDOMAIN\SSRSAdmin), but that is not what I'm looking for. How can I get this same list of permissions for MYDOMAIN\JimBob? I will not have the user's domain password, so using their credentials to call the GetPermissions() method is not an option. I am however accessing this from an account that has full permissions, so I would think that theoretically the information should be available to it.

Answer 1

SSRS gets the NT groups from the users' NT login token. This is why when you are added to a new group, you are expected to log out and back in. The same applies to most Windows checks (SQL Server, shares, NTFS etc).

If you know the NT group(s)...

You can query the ReportServer database directly. I've lifted this almost directly out of one of our reports which we use to check folder security (C.Type = 1). Filter on U.UserName.

SELECT
    R.RoleName,
    U.UserName,
    C.Path
FROM
    ReportServer.dbo.Catalog C WITH (NOLOCK)    --Parent
    JOIN
    ReportServer.dbo.Policies P WITH (NOLOCK) ON C.PolicyID = P.PolicyID
    JOIN
    ReportServer.dbo.PolicyUserRole PUR WITH (NOLOCK) ON P.PolicyID = PUR.PolicyID 
    JOIN
    ReportServer.dbo.Users U WITH (NOLOCK) ON PUR.UserID = U.UserID 
    JOIN
    ReportServer.dbo.Roles R WITH (NOLOCK) ON PUR.RoleID = R.RoleID
WHERE
    C.Type = 1 

Answer 2

look into "GetPolicies Method" you can see at the following link.

http://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.getpolicies.aspx

Answer 3

Hopefully this will get you started. I use it when copying Folder structure, and Reports from an old server to a new server when I want to 'migrate' my SSRS items from the Source to the Destination Server. It is a a Method to Get the Security Policies for an item on one server, and then set the Security Policies for an identical item on another server, after I have copied the item from the Source Server to the Destination Server. You have to set your own Source and Destination Server Names.

using System;

using System.Collections.Generic;
using System.Diagnostics;
using System.Web.Services.Protocols;    //<=== required for SoapException

namespace SSRS_WebServices_Utility
{
internal static class TEST
{


    internal static void GetPoliciesForAnItem_from_Source_ThenSetThePolicyForTheItem_on_Destination(string itemPath)
    {

        string sSourceServer = "SOURCE-ServerName";
        Source_ReportService2010.ReportingService2010 sourceRS = new Source_ReportService2010.ReportingService2010();
        sourceRS.Credentials = System.Net.CredentialCache.DefaultCredentials;
        sourceRS.Url = @"http://" + sSourceServer + "/reportserver/reportservice2010.asmx";


        string sDestinationServer = "DESTINATION-ServerName";
        Destination_ReportService2010.ReportingService2010 DestinationRS = new Destination_ReportService2010.ReportingService2010();
        DestinationRS.Credentials = System.Net.CredentialCache.DefaultCredentials;
        DestinationRS.Url = @"http://" + sDestinationServer + "/reportserver/reportservice2010.asmx";



        Boolean val = true;
        Source_ReportService2010.Policy[] curPolicy = null;
        Destination_ReportService2010.Policy[] newPolicy = null;
        try
        {

            curPolicy = new Source_ReportService2010.Policy[1];
            curPolicy = sourceRS.GetPolicies(itemPath, out val);        //e.g. of itemPath:  "/B2W/001_OLD_PuertoRicoReport"



            //DestinationRS.SetPolicies(itemPath, newPolicy);
            int iCounter = 0;
            //int iMax = curPolicy.Length;

            newPolicy = new Destination_ReportService2010.Policy[curPolicy.Length];
            foreach (Source_ReportService2010.Policy p in curPolicy)
            {
                //create the Policy
                Destination_ReportService2010.Policy pNew = new Destination_ReportService2010.Policy();
                pNew.GroupUserName = p.GroupUserName;
                pNew.GroupUserName = p.GroupUserName;
                Destination_ReportService2010.Role rNew = new Destination_ReportService2010.Role();
                rNew.Description = p.Roles[0].Description;
                rNew.Name = p.Roles[0].Name;

                //create the Role, which is part of the Policy
                pNew.Roles = new Destination_ReportService2010.Role[1];
                pNew.Roles[0]=rNew;
                newPolicy[iCounter] = pNew;
                iCounter += 1;

            }

            DestinationRS.SetPolicies(itemPath, newPolicy);

            Debug.Print("whatever");

        }
        catch (SoapException ex)
        {

            Debug.Print("SoapException: " + ex.Message);


        }
        catch (Exception Ex)
        {
            Debug.Print("NON-SoapException: " + Ex.Message);

        }

        finally
        {
            if (sourceRS != null)
                sourceRS.Dispose();
            if (DestinationRS != null)
                DestinationRS.Dispose();                    

        }
    }

}

}

To invoke it use the following:

 TEST.GetPoliciesForAnItem_from_Source_ThenSetThePolicyForTheItem_on_Destination("/FolderName/ReportName");

Where you have to put your own SSRS Folder Name and Report Name, i.e. the Path to the item.

In fact I use a method that loops through all the items in the Destination folder that then calls the method like this:

        internal static void CopyTheSecurityPolicyFromSourceToDestinationForAllItems_2010()
    {
        string sDestinationServer = "DESTINATION-ServerName";

        Destination_ReportService2010.ReportingService2010 DestinationRS = new Destination_ReportService2010.ReportingService2010();
        DestinationRS.Credentials = System.Net.CredentialCache.DefaultCredentials;
        DestinationRS.Url = @"http://" + sDestinationServer + "/reportserver/reportservice2010.asmx";

        // Return a list of catalog items in the report server database
        Destination_ReportService2010.CatalogItem[] items = DestinationRS.ListChildren("/", true);

        // For each FOLDER, debug Print some properties
        foreach (Destination_ReportService2010.CatalogItem ci in items)
        {
            {
                Debug.Print("START----------------------------------------------------");
                Debug.Print("Object Name:         " + ci.Name);
                Debug.Print("Object Type:         " + ci.TypeName);
                Debug.Print("Object Path:         " + ci.Path);
                Debug.Print("Object Description:  " + ci.Description);
                Debug.Print("Object ID:           " + ci.ID);
                Debug.Print("END----------------------------------------------------");
                try
                {
                    GetPoliciesForAnItem_from_Source_ThenSetThePolicyForTheItem_on_Destination(ci.Path);
                }
                catch (SoapException e)
                {
                    Debug.Print("SoapException START----------------------------------------------------");
                    Debug.Print(e.Detail.InnerXml);
                    Debug.Print("SoapException END----------------------------------------------------");

                }
                catch (Exception ex)
                {
                    Debug.Print("ERROR START----------------------------------------------------");
                    Debug.Print(ex.GetType().FullName);
                    Debug.Print(ex.Message);
                    Debug.Print("ERROR END----------------------------------------------------");
                }
            }
        }
    }