How to Programmatically Add a User/Group to your SSRS Report Using PowerShell

Written by belle
Posted September 10, 2010 at 9:06 pm

Some of my projects are still in SSRS 2005 and PowerShell v1.0, so this script works and is tested on this environment only, for now.
I plan to port this to SSRS2008, PowerShell v2.0 when I get the chance.

I don’t think the code will change much; although one major change would be how I’m creating the proxy right now. PowerShell v2.0 has a cmdlet called New-WebServiceProxy.

To get this to work for PS v1.0, for now, I use New-WebServiceProxy.ps1 from

http://poshcode.org/538 to create the proxy.

Steps are fairly simple
1. Create the proxy
2. Create an array of policies (ie existing users/groups for a particular report or folder)
3. Create a new policy
4. Create a new role, and add it to your policy
5. “Re”-set your policies — ex $reportserverproxy.SetPolicies($itempath,$newpolicies);

 
#Other .NET programs use ReportService2005.ReportService2005 object
#However there is no ReportService2005.dll. 
#The library is called "ReportingServicesLibrary.dll" located in
#"<install directory>Program FilesMicrosoft SQL Server
#MSSQL.3Reporting ServicesReportServerbin"
 
#make sure this is in just one line :)
[System.Reflection.Assembly]::LoadFrom("D:Program FilesMicrosoft SQL Server
MSSQL.3Reporting ServicesReportServerbinReportingServicesLibrary.dll") 
 
$server = "POSEIDON";
$uri = "http://$($server)/ReportServer/ReportService.asmx?WSDL";
 
#PowerShell v2.0 has a cmdlet called New-WebServiceProxy
#Right now for PS v1.0, to get a proxy I use
# New-WebServiceProxy.ps1 from http://poshcode.org/538
$command = "C:PowerShellNew-WebServiceProxy.ps1  $uri "
$reportserverproxy = Invoke-Expression $command
 
#Below is the C# version of creating the proxy, in case you're interested
#rs = new ReportingService2005();
 
#let's start with one report for now
$itempath = "/Sales/Sales Report by Date Range";
 
#this will hold all the group/users for a report
$newpolicies = @()
$inherit = $true;
 
#this gets all users who currently have access to this report
#need pass inherit by reference
$policies = $reportserverproxy.GetPolicies($itempath, [ref]$inherit)
 
#this will automatically break inheritance
#also note that when you programmatically mess 
#with policies, you will need to "re-add" users that were 
#already there, if you want them to keep on having access
#to your reports
 
foreach ($policy in $policies)
{
	$newpolicies += $policy
}
 
#this new "Policy" type is loaded from ReportingServicesLibrary.dll
$newpolicy = New-Object "Policy"
$newpolicy.GroupUserName = "DOMAINjdoe"; 
#if you want to prompt the user, you can use Read-Host
 
#a policy must have roles
$newrole = New-Object "Role";
$newrole.Name = "Browser";
 
#add the role to the policy
$newpolicy.roles += $newrole
 
 
#check if this user already exists in your policy array
if ($($newpolicies | %{$_.GroupUserName}) -notcontains $newpolicy.GroupUserName)
{
	$newpolicies += $newpolicy;
}
 
#set the policies
$reportserverproxy.SetPolicies($itempath,$newpolicies);
 
 
#done
#check your report now :)