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

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
#"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 :)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s