The report itself is what I need except it only shows the Domain Account ID. I need it to show the user's Display Name, their actual name.
Thanks
The report itself is what I need except it only shows the Domain Account ID. I need it to show the user's Display Name, their actual name.
Thanks
Hi,
Please find the attached updated query for the requested report. In the updated query, we have added DisplayName and FriendlyName of the DomainUser Account. Kindly replace the query on the query tab with the attached updated query and click on the Validate Query.
Once the query is updated, you can edit the layout from the layout tab.
Kindly let us know if you have any queries.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON DECLARE @SELECTED_COMPUTERS table (computer nvarchar(255)) DECLARE @SELECTED_FOLDERS table (RootPath nvarchar(max)) DECLARE @INCLUDE_SUB_FOLDERS bit DECLARE @INCLUDE_DOMAIN_USERS bit -- 1. Build list of subfolders DECLARE @FolderTable TABLE ( ComputerID BIGINT, NTFSID BINARY(32)) DECLARE @WorkingFolderTableWithSubFolders TABLE ( RootPath nvarchar(max), NTFSID BINARY(32)) -- Make a copy of the parameter table INSERT INTO @FolderTable SELECT [tblComputer].ComputerID, [tblNTFS].[NTFSID] FROM [tblNTFSComputer] base LEFT OUTER JOIN [tblNTFS] tblNTFS ON ([base].[NTFSComputerID] = [tblNTFS].[NTFSComputerID] AND [tblNTFS].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblComputer] tblComputer ON ([base].[ComputerID] = [tblComputer].[ComputerID] AND [tblComputer].[LDM_TOMBSTONED] = 0) WHERE (([tblComputer].[ComputerName] IN (SELECT * FROM @SELECTED_COMPUTERS)) AND ([tblNTFS].[Path] IN (SELECT * FROM @SELECTED_FOLDERS)) AND base.[LDM_TOMBSTONED] = 0) -- 2. Fill WorkingFolderTableWithParentFolder with all the associated NTFSID's DECLARE @tempNTFSID BINARY(32) DECLARE NTFSCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT NTFSID FROM @FolderTable OPEN NTFSCursor DECLARE @PUFQ_Break bit SELECT @PUFQ_Break = 0 WHILE @PUFQ_Break = 0 BEGIN FETCH NEXT FROM NTFSCursor INTO @tempNTFSID SELECT @PUFQ_Break = @@FETCH_STATUS IF @@FETCH_STATUS = 0 BEGIN IF @INCLUDE_SUB_FOLDERS = 1 BEGIN INSERT INTO @WorkingFolderTableWithSubFolders SELECT NTFS.Path, GANUN.NTFSID FROM dbo.GetAllNTFSUnderNTFS(@tempNTFSID) GANUN INNER JOIN tblNTFS NTFS ON GANUN.NTFSID = NTFS.NTFSID INNER JOIN tblNTFSComputer NTFSComputer ON NTFS.NTFSComputerID = NTFSComputer.NTFSComputerID INNER JOIN tblComputer C ON NTFSComputer.ComputerID = C.ComputerID WHERE C.ComputerID IN (SELECT ComputerID FROM @FolderTable) AND NTFS.IsFolder = 1 END ELSE BEGIN INSERT INTO @WorkingFolderTableWithSubFolders SELECT TOP 1 tblNTFS.Path, @tempNTFSID FROM tblNTFS WHERE tblNTFS.NTFSID = @tempNTFSID END END END CLOSE NTFSCursor DEALLOCATE NTFSCursor DECLARE @ReturnTable TABLE ( AccountID bigint, ComputerName nvarchar(255), FullPath nvarchar(max), AccountDisplayName nvarchar(255), Perm nvarchar(255), [DisplayName] nvarchar(255), [FriendlyName] nvarchar(255)) DECLARE @ProcessedGroups TABLE ( AccountID bigint ) DECLARE @PathID bigint DECLARE @RootPath nvarchar(max) DECLARE @AccountID bigint DECLARE @ComputerName nvarchar(255) DECLARE @FullPath nvarchar(max) DECLARE @AccountType nvarchar(5) DECLARE @AccountDisplayName nvarchar(255) DECLARE @AceTypeText nvarchar(255) DECLARE @PermText nvarchar(50) DECLARE @PermTextExt nvarchar(305) DECLARE @AccountSid nvarchar (50) DECLARE @DisplayName nvarchar(255) DECLARE @FriendlyName nvarchar(255) DECLARE @ENPQ_Folder_Break bit DECLARE @ENPQ_Account_Break bit -- Declare folder cursor DECLARE FolderCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT RootPath, NTFSID FROM @WorkingFolderTableWithSubFolders -- End of Folder Cursor declaration OPEN FolderCursor SELECT @ENPQ_Folder_Break = 0 WHILE @ENPQ_Folder_Break = 0 BEGIN FETCH NEXT FROM FolderCursor INTO @RootPath, @PathID SELECT @ENPQ_Folder_Break = @@FETCH_STATUS IF @@FETCH_STATUS = 0 BEGIN -- Declare account cursor DECLARE AccountCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT [tblComputer].[ComputerName] as [Computer Name (Computer)], [tblNTFS].[Path] as [Path (NTFS)], [tblACE].[AccountID], [vwACEAccount].AccountType, [vwACEAccount].AccountSid, CASE WHEN LEN([vwACEAccount].[SAMAccountDomain]) = 0 THEN [vwACEAccount].[SAMAccountName] ELSE [vwACEAccount].[SAMAccountDomain] + '\' + [vwACEAccount].[SAMAccountName] END as [Account Name (Account)], [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)], tblDomainUser.DisplayName, tblDomainUser.FriendlyName --DATEADD(minute, -180, [base].[CollectedTime])as [Last Collected Time (Computer)] FROM [tblNTFSComputer] base LEFT OUTER JOIN [tblNTFS] tblNTFS ON ([base].[NTFSComputerID] = [tblNTFS].[NTFSComputerID] AND [tblNTFS].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblACL] tblACL ON ([tblNTFS].[ACLID] = [tblACL].[ACLID] AND [tblACL].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblAccount] tblAccount ON ([tblACL].[OwnerAccountID] = [tblAccount].[AccountID] AND [tblAccount].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblACE] tblACE ON ([tblACL].[ACLID] = [tblACE].[ACLID] AND [tblACE].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [vwACEAccount] vwACEAccount ON ([tblACE].[AccountID] = [vwACEAccount].[AccountID]) LEFT OUTER JOIN [tblDomainGroup] tblDomainGroup ON ([vwACEAccount].[AccountID] = [tblDomainGroup].[AccountID] AND [tblDomainGroup].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblComputerUser] tblComputerUser ON ([vwACEAccount].[AccountID] = [tblComputerUser].[AccountID] AND [tblComputerUser].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblDomainUser] tblDomainUser ON ([vwACEAccount].[AccountID] = [tblDomainUser].[AccountID] AND [tblDomainUser].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [vwComputerGroup] vwComputerGroup ON ([vwACEAccount].[AccountID] = [vwComputerGroup].[AccountID]) LEFT OUTER JOIN [tblComputer] tblComputer ON ([base].[ComputerID] = [tblComputer].[ComputerID] AND [tblComputer].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblAccount] tblAccount_2 ON ([tblComputerUser].[AccountID] = [tblAccount_2].[AccountID] AND [tblAccount_2].[LDM_TOMBSTONED] = 0) WHERE ([tblNTFS].[IsFolder] = 1 AND base.[LDM_TOMBSTONED] = 0) AND ([tblComputer].[ComputerName] IN (SELECT computer from @SELECTED_COMPUTERS)) AND ([tblNTFS].[Path] IN (SELECT RootPath from @SELECTED_FOLDERS)) ORDER BY [tblComputer].[ComputerName] ASC, [Permissions (ACE)] ASC -- End of Account Cursor declaration OPEN AccountCursor SELECT @ENPQ_Account_Break = 0 WHILE @ENPQ_Account_Break = 0 BEGIN FETCH NEXT FROM AccountCursor INTO @ComputerName, @FullPath, @AccountID, @AccountType, @AccountSid, @AccountDisplayName, @AceTypeText, @PermText, @DisplayName, @FriendlyName SELECT @ENPQ_Account_Break = @@FETCH_STATUS IF @@FETCH_STATUS = 0 BEGIN SELECT @PermTextExt = @AceTypeText + ' ' + @PermText IF @AccountType = 'G' BEGIN -- TODO: insert only once, do not allow duplicates INSERT INTO @ReturnTable SELECT distinct GM.AccountID, @ComputerName, @RootPath, GM.AccountName, @PermTextExt, DU.DisplayName, DU.FriendlyName FROM vwGroupMemberAccount2 GM LEFT OUTER JOIN tblDomainUser DU on GM.AccountID = DU.AccountID and DU.[LDM_TOMBSTONED] = 0 WHERE GM.GroupID IN (SELECT GroupAccountID FROM dbo.ExpandedGroupMembershipForAccountID(@AccountID, @INCLUDE_DOMAIN_USERS)) AND GM.AccountType <> 'G' AND GM.AccountID not in (select R.AccountID from @ReturnTable R) END ELSE if (Not exists (select 1 from @ReturnTable where AccountId = @AccountID)) begin -- TODO: insert only once, do not allow duplicates INSERT INTO @ReturnTable Values (@AccountId, @ComputerName, @RootPath, @AccountDisplayName, @PermTextExt, @DisplayName, @FriendlyName) end END END CLOSE AccountCursor DEALLOCATE AccountCursor END END CLOSE FolderCursor DEALLOCATE FolderCursor -- because abovemetioned TODO is not implemented so just use DISTINCT to get rid of duplicates SELECT ComputerName as [Computer Name] , FullPath as [Path], AccountDisplayName as [Account Name], Perm as [Permissions], [DisplayName], [FriendlyName] FROM @ReturnTable
Thanks
Naureen
Hi,
Please find the attached updated query for the requested report. In the updated query, we have added DisplayName and FriendlyName of the DomainUser Account. Kindly replace the query on the query tab with the attached updated query and click on the Validate Query.
Once the query is updated, you can edit the layout from the layout tab.
Kindly let us know if you have any queries.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON DECLARE @SELECTED_COMPUTERS table (computer nvarchar(255)) DECLARE @SELECTED_FOLDERS table (RootPath nvarchar(max)) DECLARE @INCLUDE_SUB_FOLDERS bit DECLARE @INCLUDE_DOMAIN_USERS bit -- 1. Build list of subfolders DECLARE @FolderTable TABLE ( ComputerID BIGINT, NTFSID BINARY(32)) DECLARE @WorkingFolderTableWithSubFolders TABLE ( RootPath nvarchar(max), NTFSID BINARY(32)) -- Make a copy of the parameter table INSERT INTO @FolderTable SELECT [tblComputer].ComputerID, [tblNTFS].[NTFSID] FROM [tblNTFSComputer] base LEFT OUTER JOIN [tblNTFS] tblNTFS ON ([base].[NTFSComputerID] = [tblNTFS].[NTFSComputerID] AND [tblNTFS].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblComputer] tblComputer ON ([base].[ComputerID] = [tblComputer].[ComputerID] AND [tblComputer].[LDM_TOMBSTONED] = 0) WHERE (([tblComputer].[ComputerName] IN (SELECT * FROM @SELECTED_COMPUTERS)) AND ([tblNTFS].[Path] IN (SELECT * FROM @SELECTED_FOLDERS)) AND base.[LDM_TOMBSTONED] = 0) -- 2. Fill WorkingFolderTableWithParentFolder with all the associated NTFSID's DECLARE @tempNTFSID BINARY(32) DECLARE NTFSCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT NTFSID FROM @FolderTable OPEN NTFSCursor DECLARE @PUFQ_Break bit SELECT @PUFQ_Break = 0 WHILE @PUFQ_Break = 0 BEGIN FETCH NEXT FROM NTFSCursor INTO @tempNTFSID SELECT @PUFQ_Break = @@FETCH_STATUS IF @@FETCH_STATUS = 0 BEGIN IF @INCLUDE_SUB_FOLDERS = 1 BEGIN INSERT INTO @WorkingFolderTableWithSubFolders SELECT NTFS.Path, GANUN.NTFSID FROM dbo.GetAllNTFSUnderNTFS(@tempNTFSID) GANUN INNER JOIN tblNTFS NTFS ON GANUN.NTFSID = NTFS.NTFSID INNER JOIN tblNTFSComputer NTFSComputer ON NTFS.NTFSComputerID = NTFSComputer.NTFSComputerID INNER JOIN tblComputer C ON NTFSComputer.ComputerID = C.ComputerID WHERE C.ComputerID IN (SELECT ComputerID FROM @FolderTable) AND NTFS.IsFolder = 1 END ELSE BEGIN INSERT INTO @WorkingFolderTableWithSubFolders SELECT TOP 1 tblNTFS.Path, @tempNTFSID FROM tblNTFS WHERE tblNTFS.NTFSID = @tempNTFSID END END END CLOSE NTFSCursor DEALLOCATE NTFSCursor DECLARE @ReturnTable TABLE ( AccountID bigint, ComputerName nvarchar(255), FullPath nvarchar(max), AccountDisplayName nvarchar(255), Perm nvarchar(255), [DisplayName] nvarchar(255), [FriendlyName] nvarchar(255)) DECLARE @ProcessedGroups TABLE ( AccountID bigint ) DECLARE @PathID bigint DECLARE @RootPath nvarchar(max) DECLARE @AccountID bigint DECLARE @ComputerName nvarchar(255) DECLARE @FullPath nvarchar(max) DECLARE @AccountType nvarchar(5) DECLARE @AccountDisplayName nvarchar(255) DECLARE @AceTypeText nvarchar(255) DECLARE @PermText nvarchar(50) DECLARE @PermTextExt nvarchar(305) DECLARE @AccountSid nvarchar (50) DECLARE @DisplayName nvarchar(255) DECLARE @FriendlyName nvarchar(255) DECLARE @ENPQ_Folder_Break bit DECLARE @ENPQ_Account_Break bit -- Declare folder cursor DECLARE FolderCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT RootPath, NTFSID FROM @WorkingFolderTableWithSubFolders -- End of Folder Cursor declaration OPEN FolderCursor SELECT @ENPQ_Folder_Break = 0 WHILE @ENPQ_Folder_Break = 0 BEGIN FETCH NEXT FROM FolderCursor INTO @RootPath, @PathID SELECT @ENPQ_Folder_Break = @@FETCH_STATUS IF @@FETCH_STATUS = 0 BEGIN -- Declare account cursor DECLARE AccountCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT [tblComputer].[ComputerName] as [Computer Name (Computer)], [tblNTFS].[Path] as [Path (NTFS)], [tblACE].[AccountID], [vwACEAccount].AccountType, [vwACEAccount].AccountSid, CASE WHEN LEN([vwACEAccount].[SAMAccountDomain]) = 0 THEN [vwACEAccount].[SAMAccountName] ELSE [vwACEAccount].[SAMAccountDomain] + '\' + [vwACEAccount].[SAMAccountName] END as [Account Name (Account)], [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)], tblDomainUser.DisplayName, tblDomainUser.FriendlyName --DATEADD(minute, -180, [base].[CollectedTime])as [Last Collected Time (Computer)] FROM [tblNTFSComputer] base LEFT OUTER JOIN [tblNTFS] tblNTFS ON ([base].[NTFSComputerID] = [tblNTFS].[NTFSComputerID] AND [tblNTFS].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblACL] tblACL ON ([tblNTFS].[ACLID] = [tblACL].[ACLID] AND [tblACL].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblAccount] tblAccount ON ([tblACL].[OwnerAccountID] = [tblAccount].[AccountID] AND [tblAccount].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblACE] tblACE ON ([tblACL].[ACLID] = [tblACE].[ACLID] AND [tblACE].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [vwACEAccount] vwACEAccount ON ([tblACE].[AccountID] = [vwACEAccount].[AccountID]) LEFT OUTER JOIN [tblDomainGroup] tblDomainGroup ON ([vwACEAccount].[AccountID] = [tblDomainGroup].[AccountID] AND [tblDomainGroup].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblComputerUser] tblComputerUser ON ([vwACEAccount].[AccountID] = [tblComputerUser].[AccountID] AND [tblComputerUser].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblDomainUser] tblDomainUser ON ([vwACEAccount].[AccountID] = [tblDomainUser].[AccountID] AND [tblDomainUser].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [vwComputerGroup] vwComputerGroup ON ([vwACEAccount].[AccountID] = [vwComputerGroup].[AccountID]) LEFT OUTER JOIN [tblComputer] tblComputer ON ([base].[ComputerID] = [tblComputer].[ComputerID] AND [tblComputer].[LDM_TOMBSTONED] = 0) LEFT OUTER JOIN [tblAccount] tblAccount_2 ON ([tblComputerUser].[AccountID] = [tblAccount_2].[AccountID] AND [tblAccount_2].[LDM_TOMBSTONED] = 0) WHERE ([tblNTFS].[IsFolder] = 1 AND base.[LDM_TOMBSTONED] = 0) AND ([tblComputer].[ComputerName] IN (SELECT computer from @SELECTED_COMPUTERS)) AND ([tblNTFS].[Path] IN (SELECT RootPath from @SELECTED_FOLDERS)) ORDER BY [tblComputer].[ComputerName] ASC, [Permissions (ACE)] ASC -- End of Account Cursor declaration OPEN AccountCursor SELECT @ENPQ_Account_Break = 0 WHILE @ENPQ_Account_Break = 0 BEGIN FETCH NEXT FROM AccountCursor INTO @ComputerName, @FullPath, @AccountID, @AccountType, @AccountSid, @AccountDisplayName, @AceTypeText, @PermText, @DisplayName, @FriendlyName SELECT @ENPQ_Account_Break = @@FETCH_STATUS IF @@FETCH_STATUS = 0 BEGIN SELECT @PermTextExt = @AceTypeText + ' ' + @PermText IF @AccountType = 'G' BEGIN -- TODO: insert only once, do not allow duplicates INSERT INTO @ReturnTable SELECT distinct GM.AccountID, @ComputerName, @RootPath, GM.AccountName, @PermTextExt, DU.DisplayName, DU.FriendlyName FROM vwGroupMemberAccount2 GM LEFT OUTER JOIN tblDomainUser DU on GM.AccountID = DU.AccountID and DU.[LDM_TOMBSTONED] = 0 WHERE GM.GroupID IN (SELECT GroupAccountID FROM dbo.ExpandedGroupMembershipForAccountID(@AccountID, @INCLUDE_DOMAIN_USERS)) AND GM.AccountType <> 'G' AND GM.AccountID not in (select R.AccountID from @ReturnTable R) END ELSE if (Not exists (select 1 from @ReturnTable where AccountId = @AccountID)) begin -- TODO: insert only once, do not allow duplicates INSERT INTO @ReturnTable Values (@AccountId, @ComputerName, @RootPath, @AccountDisplayName, @PermTextExt, @DisplayName, @FriendlyName) end END END CLOSE AccountCursor DEALLOCATE AccountCursor END END CLOSE FolderCursor DEALLOCATE FolderCursor -- because abovemetioned TODO is not implemented so just use DISTINCT to get rid of duplicates SELECT ComputerName as [Computer Name] , FullPath as [Path], AccountDisplayName as [Account Name], Perm as [Permissions], [DisplayName], [FriendlyName] FROM @ReturnTable
Thanks
Naureen