Need a small mod for a built in report. User Permissions on Folders

Question

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

Naureen.Ali · Accepted Answer

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. 
 
 Updated Query.txt 
 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

Need a small mod for a built in report. User Permissions on Folders

Top Replies