How can I find which AD Groups are assigned to Software Packages in the Software Portal?
How can I extract data from the database that will show AD permissions (User/Group Name) for the SW Portal software packages that are posted?
ITMS 8.x
This SQL Query will return the SID of the AD Group for each SW Package in the Portal:
select distinct i.name, ps.TrusteeSid, ti.Name, rk.*
from SWP_PublishingItemSetting ps
left join ResourceKey rk
on rk.KeyValue = ps.TrusteeSid
join item i
on i.Guid = ps.PublishingItemGuid
left join vItem ti
on ti.Guid = rk.ResourceGuid
To dig further, please Add the "Active Directory Module for Windows Powershell" in System Manager so that AD queries can be run. This also allows AD Queries to be run from SQL. Instructions on how to install this Feature:
How to Install the PowerShell Active Directory Module and Manage AD
Then run these commands in SQL to open up the option to run AD Commands in SQL:
EXEC sp_configure 'show advanced options', 1;
GO
Reconfigure;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
Reconfigure
GO
And at this point we can execute AD commands in SQL using Powershell:
xp_cmdshell 'powershell.exe -command Get-ADGroup -Identity S-1-5-21-4233680027-2847814550-3527121473-513'
Note: Enabling 'xp_cmdshell' is considered a security risk per many companies since it grant you the ability for running anything with elevated rights as if you were in a command prompt.