Search This Blog

Monday, 9 March 2015

Last ran Query in Sql Navigator

There are two ways to find past ran queries in Sql Navigator:

1) Click (see image-1) on SQL History or press (CTRL+ALT+R) 
2)Find the path …\SQL Navigator 6.2.1\Unified Editor\history in your system.

Thursday, 5 March 2015

SharePoint Items Security using SharePoint Powershell

We can use following SharePoint PowerShell script to pull all SharePoint items security whether an item is using unique permission or inherited permission. The output will be written in a .csv file “SharepointSitesOutput.csv”

Just copy and paste following code in .ps1 file and execute that file on SharePoint PowerShell commad using commad &<filename.ps1>

get-spsite -Limit All|get-spweb -Limit All|Select URL,Title, hasUniquePerm |Export-csv SharepointSitesOutput.csv –NoTypeInformation

List SSRS items Permissions using PoweShell

If we pull SSRS Items security using ReportServer database using following query then we get stale information. It includes those users as well that has been deleted/deactivated in Active directory.

select C.UserName, D.RoleName, D.Description, E.Path, E.Name
from dbo.PolicyUserRole A
   inner join dbo.Policies B on A.PolicyID = B.PolicyID
   inner join dbo.Users C on A.UserID = C.UserID
   inner join dbo.Roles D on A.RoleID = D.RoleID
   inner join dbo.Catalog E on A.PolicyID = E.PolicyID
order by C.UserName

So instead of using query at ReportServer Database, we can use reportservice2005.asmx GetPolicies method. Following is the Powershell Script that writes the SSRS Folders permissions in SSRSSecurityOutput.csv. Just copy and paste the following code in a .ps1 file like SSRSPermissions.ps1.
.Ps1 is file extension for poweshell script.

$ReportServerUri = 'http://<ReportServer>/ReportServer/ReportService2005.asmx'
$InheritParent = $true
$SourceFolderPath = '/'

$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential
$items = $Proxy.ListChildren($sourceFolderPath, $true)|Select-Object Type, Path, Name|Where-Object {$_.type -eq "Folder"};
foreach($item in $items)
Add-Member -InputObject $item -MemberType NoteProperty -Name UserName -Value '';
foreach($policy in $Proxy.GetPolicies($item.path, [ref]$InheritParent))
                $outSSRSSecurity += $objtemp;
$outSSRSSecurity|Export-csv SSRSSecurityOutput.csv -NoTypeInformation;