Advanced Security Auditing
Tue 18 Dec 2018 - 10:25
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!
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
Re: Advanced Security Auditing
Tue 18 Dec 2018 - 10:38
Original post by Steven Rodney: srodney@kingsleynapley.co.uk
Here is a script we are using Jamie...
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
Permissions in this forum:
You cannot reply to topics in this forum
|
|