Show items permissions, starting from Security Groups

[Houdini]

OK, you need access to the MS SQL database for this. Don't ask me about warranty, and don't ask me about what happens after an update...

First, create a stored procedure to iterate through the item hierarchy. This is a simple CTE and not tuned for high performance.

Code:

CREATE   FUNCTION dbo.ItemHierarchy  (@itemid uniqueidentifier)
  RETURNS VARCHAR(8000)
  AS
BEGIN
  DECLARE @Result VARCHAR(1000)
;WITH CTE AS
    (
    SELECT [Level]=1, parentitemid, itemID, Path= CAST(text AS VARCHAR(8000))
    FROM dbo.Items
    WHERE itemid = @itemid
    UNION ALL
    SELECT [Level]+1, a.parentitemid, a.itemID, Path= CAST(text AS VARCHAR(8000)) + '/' + Path
    FROM dbo.Items a
    JOIN CTE b ON b.parentitemid = a.itemID
    )
    SELECT @Result = path from CTE where level = (select max(level) from CTE);
    RETURN @Result
END;

Then you can get the assigned credentials and connections with these statements:

Code:

SELECT * FROM dbo.securityGroups;

SELECT distinct dbo.itemhierarchy(sa.itemid) AS path, sg.groupname
from dbo.SecurityAssignment sa, dbo.securitygroups sg
WHERE sa.groupid=sg.groupid
AND sa.groupid='00000000-0000-0000-0000-000000000000'
ORDER BY 1;

(replace the 000... with the GUID of your SecGroup).

If I analysed the results correctly, it shows all items (credentials, connections, folders) where individual security settings are done (Default Values -> Data exists), not where values are inherited. The statement does not show the individual settings, if you are a bit familiar with SQL you can edit the second statement to include the "SecurityIdent" column.

This works for me on:
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
ASG 2021 - 14.07241.1
DBeaver 22.0