Advanced Security Auditing

Original post by Jamie Francis: jamie.francis@emwllp.com

Hi

Does anyone have a SQL script to pull out all matters & associated security groups from the advanced security module?

I have managed to get some of the information from the database using the script below, but the ones I checked showed different security groups/people in the security tab. I guess I'm missing something. I did try to look at the SP for the security tab but it doesn't appear to exist in the database!

Code:


SELECT
    c.clNo [Client Number]
    ,c.clno + '.' + f.fileNo [Matter Number]
    ,c.clName [Client Name]
    ,f.fileDesc [Matter Desc]
    ,g.Description [Group]
    ,g.ADDistinguishedName [DN]
FROM
    [relationship].[UserGroup_File] u
        JOIN
    [item].[Group] g
        ON g.id = u.UserGroupID
        JOIN
    dbo.dbFile f
        ON f.fileID = u.fileID
        JOIN
    dbo.dbclient c
        ON c.clid = u.clid
ORDER BY
    [Client Name] ASC


Original post by Steven Rodney: srodney@kingsleynapley.co.uk

Here is a script we are using Jamie...

Code:

SELECT
    x1.fileID
    , x1.clid
    , x1.clientNumber
    , x1.matterRef
    , x1.clientName
    , x1.matterDesc
    , x1.[Desc]
    , x1.Name
    , x1.ad
    , x1.DescActive
FROM
    (SELECT
        NULL AS [fileID]
        , a1.clid
        , a1.clno AS [clientNumber]
        , NULL AS [matterRef]
        , a1.clname AS [clientName]
        , NULL AS [matterDesc]
        , z1.[Desc]
        , z1.Name
        , z1.ad
        , z1.Active AS [DescActive]
        , 1 AS [ordinal2]
    FROM
        dbclient a1
            LEFT JOIN
        [relationship].[UserGroup_Client] d1
            ON d1.ClientID = a1.clID
        LEFT JOIN
    (SELECT
        b1.id
        , b1.Description AS [Desc]
        , 'AD_GROUP' AS [Name]
        , b1.Active AS [Active]
        , b1.ADDistinguishedName AS [AD]
        , 1 AS [Ordinal]
    FROM
        [item].[Group] b1
            UNION
    SELECT
        c1.ID
        , c1.NTLogin AS [Desc]
        , c1.Name AS [Name]
        , c1.active
        , NULL AS [AD]
        , 2 AS [Ordinal]
    FROM
        [item].[User] c1) z1
        ON z1.ID = d1.UserGroupID
    WHERE
        1 = 1
        AND COALESCE(CAST(z1.ID AS NVARCHAR(4000)), '') != ''
            UNION
    SELECT
        a1.fileID
        , b1.clid
        , b1.clNo as [clientNumber]
        , b1.clNo + N'.' + a1.fileno AS [matterRef]
        , b1.clName AS [clientName]
        , a1.fileDesc AS [matterDesc]
        , z1.[Desc]
        , z1.Name
        , z1.ad
        , z1.Active AS [DescActive]
        , 2 AS [ordinal2]
    FROM
        dbfile a1
            INNER JOIN
        dbClient b1
            ON b1.clid = a1.clid
            LEFT JOIN
        [relationship].[UserGroup_File] e1
            ON e1.FileID = a1.fileID
        LEFT JOIN
    (SELECT
        b1.id
        , b1.Description AS [Desc]
        , 'AD_GROUP' AS [Name]
        , b1.Active AS [Active]
        , b1.ADDistinguishedName AS [AD]
        , 1 AS [Ordinal]
    FROM
        [item].[Group] b1
            UNION
    SELECT
        c1.ID
        , c1.NTLogin AS [Desc]
        , c1.Name AS [Name]
        , c1.active
        , NULL AS [AD]
        , 2 AS [Ordinal]
    FROM
        [item].[User] c1) z1
    ON z1.ID = e1.UserGroupID
    WHERE
    1 = 1
    AND COALESCE(CAST(z1.ID AS NVARCHAR(4000)), '') != '') x1
WHERE
    1 = 1
ORDER BY
    x1.clientNumber ASC
    , x1.clid ASC
    , x1.ordinal2 ASC
    , x1.fileID ASC
    , x1.matterRef ASC
    , x1.[Desc] ASC