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

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

Top Replies

Naureen.Ali over 5 years ago +1 verified

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…

Parents

+1 Naureen.Ali over 5 years ago

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.

Fullscreen Updated Query.txt Download

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

Reply

+1 Naureen.Ali over 5 years ago

Hi,

Once the query is updated, you can edit the layout from the layout tab.

Kindly let us know if you have any queries.

Fullscreen Updated Query.txt Download

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

Children

No Data