Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
$RBAC_CollectionQuery = @"
declare @SID varbinary(39), @Token nvarchar(max), @UserID nvarchar(max)
SET @SID = SUSER_SID()
Set @Token = 'S-1-5-21-'
+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),'0x'+sys.fn_varbintohexsubstring(0,@SID,13,4),1)) as varbinary(4)) as bigint) as varchar(10))
+'-'+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),'0x'+sys.fn_varbintohexsubstring(0,@SID,17,4),1)) as varbinary(4)) as bigint) as varchar(10))
+'-'+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),'0x'+sys.fn_varbintohexsubstring(0,@SID,21,4),1)) as varbinary(4)) as bigint) as varchar(10))
+'-'+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),'0x'+sys.fn_varbintohexsubstring(0,@SID,25,4),1)) as varbinary(4)) as bigint) as varchar(10))
set @UserID = dbo.fn_rbac_GetAdminIDsfromUserSIDs(@Token)
Select
cols.SiteID AS CollectionID, cols.CollectionName, cols.LimitToCollectionID, cols.LimitToCollectionName
From
dbo.fn_rbac_Collections(@UserID) cols
order by
cols.CollectionName
"@
$CollectionInfo = Invoke-DBAQuery -SqlInstance $SQLServer -Database $Database -Query $RBAC_CollectionQuery
#endregion RBAC enforced query to retrieve CollectionID and CollectionName
#region Generate dynamic parameters
$Position = 2
foreach ($Var in @('CollectionID', 'CollectionName', 'LimitToCollectionID', 'LimitToCollectionName')) {
$Set = $(@($CollectionInfo.$Var) | Where-Object { if ($null -ne $PSItem) {
$PSItem.ToString().Trim()
} } | Select-Object -Unique)
$newDynamicParamSplat = @{
Position = $Position++
HelpMessage = "Filter collection summary results by the $Var field"
ParameterSetName = [string]::Format('FilterBy{0}', $Var)
DPDictionary = $Dictionary
Mandatory = $true
Type = [string[]]
ValidateSet = $Set
Name = $Var
}
New-DynamicParam @newDynamicParamSplat
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment