Report Title: Folder Permissions with Direct Membership by Display Name
Report Description: Shows all folder permissions for the selected computers and paths.
Report Category: NTFS
Version: 2.0.1
Customization Details: The current "Folder Permissions with Membership" report doesn't have the ability to show the display name of the domain account instead of the account name. This is custom query report will allow you to achieve this. If the display name is empty or null, then the account name will be shown. Please note, that this report only return the direct members of the permissioned groups. The report is using the same query from the "Folder Permissions with Membership" report but also has the following added to it:
-- Obtain extended user/group information such as display name for the permissionsed accounts
LEFT OUTER JOIN [tblDomainGroup] DomGroupInfo
ON ([tblAccount_1].[AccountID] = [DomGroupInfo].[AccountID])
LEFT OUTER JOIN [tblDomainUser] DomUserInfo
ON ([tblAccount_1].[AccountID] = [DomUserInfo].[AccountID])
-- Obtain extended user/group information such as display name for the first level of members
LEFT OUTER JOIN [tblDomainGroup] vwDomGroupInfo
ON ([vwAccountMembers].[MemberAccountID] = [vwDomGroupInfo].[AccountID])
LEFT OUTER JOIN [tblDomainUser] vwDomUserInfo
ON ([vwAccountMembers].[MemberAccountID] = [vwDomUserInfo].[AccountID])
To create the new report:
1. Launch the Report Manager
2. Right click on “My Reports” and select “Create Custom Query Report…”
3. Type in the report name, author and description
4. Click on the “Query” tab and paste the following:
DECLARE @computerName NVARCHAR(255)
DECLARE @path NVARCHAR(MAX)
DECLARE @isExplicit bit
SELECT [tblComputer].[ComputerName] as [Computer Name (Computer)],
DomUserInfo.[DisplayName] as [Display Name (Domain User)], DomGroupInfo.[DisplayName] as [Display Name (Domain Group)],
vwDomUserInfo.[DisplayName] as [Display Name (Member Domain User)], vwDomGroupInfo.[DisplayName] as [Display Name (Member Domain Group)],
[tblNTFS].[Path] as [Path (NTFS)], [tblACE].[IsInherited] as [Is Inherited (ACE)], [tblNTFS].[IsFolder] as [Is Folder (NTFS)], [Type (ACE)] =
CASE [tblACE].[AceType]
WHEN 0 THEN N'Allow'
WHEN 1 THEN N'Deny'
ELSE ''
END, dbo.udfBitmaskLookup('ACE', 'AccessMask', 1033, [tblACE].[AccessMask]) as [Permissions (ACE)], [Applies To (ACE)] =
CASE [tblACE].[AppliesTo]
WHEN 1 THEN case [tblNTFS].[IsFolder] When 1 THEN N'This folder only' Else N'This object only' END
WHEN 2 THEN N'Subfolders only'
WHEN 3 THEN N'This folder and subfolders'
WHEN 4 THEN N'Files only'
WHEN 5 THEN N'This folder and files'
WHEN 6 THEN N'This subfolder and files only'
WHEN 7 THEN N'This folder, subfolder and files'
ELSE ''
END, CASE WHEN LEN([tblAccount_1].[SAMAccountDomain]) = 0 THEN [tblAccount_1].[SAMAccountName] ELSE [tblAccount_1].[SAMAccountDomain] + '\' + [tblAccount_1].[SAMAccountName] END as [Account Name (Account)], [vwAccountMembers].[MemberAccountName] as [Member Name (Account Member)], [vwAccountMembers].[MemberAccountType] as [Member Type (Account Member)], DATEADD(minute, -240, [base].[CollectedTime])as [Last Collected Time (Computer)], [vwAccountMembers].[MemberAccountID] as [Member Account ID (Account Member)]
FROM
[tblNTFSComputer] base
LEFT OUTER JOIN [tblNTFS] tblNTFS
ON ([base].[NTFSComputerID] = [tblNTFS].[NTFSComputerID] AND ([tblNTFS].[LDM_TOMBSTONED] = 0 OR [tblNTFS].[LDM_TOMBSTONED] IS NULL))
LEFT OUTER JOIN [tblACL] tblACL
ON ([tblNTFS].[ACLID] = [tblACL].[ACLID] AND ([tblACL].[LDM_TOMBSTONED] = 0 OR [tblACL].[LDM_TOMBSTONED] IS NULL))
LEFT OUTER JOIN [tblAccount] tblAccount
ON ([tblACL].[OwnerAccountID] = [tblAccount].[AccountID] AND ([tblAccount].[LDM_TOMBSTONED] = 0 OR [tblAccount].[LDM_TOMBSTONED] IS NULL))
LEFT OUTER JOIN [tblACE] tblACE
ON ([tblACL].[ACLID] = [tblACE].[ACLID] AND ([tblACE].[LDM_TOMBSTONED] = 0 OR [tblACE].[LDM_TOMBSTONED] IS NULL))
LEFT OUTER JOIN [tblAccount] tblAccount_1
ON ([tblACE].[AccountID] = [tblAccount_1].[AccountID] AND ([tblAccount_1].[LDM_TOMBSTONED] = 0 OR [tblAccount_1].[LDM_TOMBSTONED] IS NULL))-- Obtain extended user/group information such as display name for the permissionsed accounts
LEFT OUTER JOIN [tblDomainGroup] DomGroupInfo
ON ([tblAccount_1].[AccountID] = [DomGroupInfo].[AccountID])
LEFT OUTER JOIN [tblDomainUser] DomUserInfo
ON ([tblAccount_1].[AccountID] = [DomUserInfo].[AccountID])
LEFT OUTER JOIN [vwAccountMembers] vwAccountMembers
ON ([tblAccount_1].[AccountID] = [vwAccountMembers].[GroupAccountID])
-- Obtain extended user/group information such as display name for the first level of members
LEFT OUTER JOIN [tblDomainGroup] vwDomGroupInfo
ON ([vwAccountMembers].[MemberAccountID] = [vwDomGroupInfo].[AccountID])
LEFT OUTER JOIN [tblDomainUser] vwDomUserInfo
ON ([vwAccountMembers].[MemberAccountID] = [vwDomUserInfo].[AccountID])
LEFT OUTER JOIN [tblComputer] tblComputer
ON ([base].[ComputerID] = [tblComputer].[ComputerID] AND ([tblComputer].[LDM_TOMBSTONED] = 0 OR [tblComputer].[LDM_TOMBSTONED] IS NULL))
WHERE ([tblComputer].[ComputerName] LIKE @computerName AND [tblNTFS].[Path] LIKE @path AND [tblNTFS].[IsFolder] = 1 AND (base.LDM_TOMBSTONED = 0 OR base.LDM_TOMBSTONED IS NULL))
ORDER BY [tblComputer].[ComputerName] ASC, [tblNTFS].[Path] ASC, [tblACE].[IsInherited] ASC, [tblNTFS].[IsFolder] ASC, [Permissions (ACE)] ASC, [Applies To (ACE)] ASC, [Account Name (Account)] ASC, [vwAccountMembers].[MemberAccountName] ASC
5. Click on "Validate Query" button
6. Click on the “Parameter” tab and add the following parameter:
Prompt: Specify the computer:
Parameter: @computerName
Operator: Equals
Prompt: Specify the folder path:
Parameter: @path
Operator: Equals
7. Click on the “Layout” tab and create the layout using the layout designer (This will be similar to the existing "Folder Permissions with Membership" report layout").
NOTE: Originally created on: Mar 31, 2014 1:27 PM by SAMER ELKHATIB - Last Modified: Mar 31, 2014 1:33 PM by SAMER ELKHATIB
