Finding which AD Groups are assigned to Software Packages in the Software Portal
search cancel

Finding which AD Groups are assigned to Software Packages in the Software Portal

book

Article ID: 207810

calendar_today

Updated On:

Products

IT Management Suite Software Management Solution

Issue/Introduction

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?  

Environment

ITMS 8.x

Resolution

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.