Isolated Share Permissions

Report Title: Isolated Share Permissions
Report Description: This report shows all the shares and their permissions excluding the one's where the specified account has permissions.
Report Category: Computer

Version: 2.5

Customization Details: The existing out of box reports doesn't have the ability to return all the shares and their permissions except the share/s where a particular account (user input) has permissions. This custom query report has been created to achieve that.

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 @DomainName nvarchar(512);
DECLARE @ComputerName TABLE ( ComputerName nvarchar(255));
DECLARE @AccounttoExclude nvarchar(512);
DECLARE @Account nvarchar(512);
DECLARE @Domain nvarchar(512);

SELECT @Domain = SUBSTRING ( @AccounttoExclude ,1 , CHARINDEX('\',@AccounttoExclude)-1 );
SELECT @Account = SUBSTRING ( @AccounttoExclude, CHARINDEX('\', @AccounttoExclude)+1, len(@AccounttoExclude));

With ALL_SHARE_PERMISSIONS as
(
       SELECT [tblComputer].[DomainName] as [Domain (Computer)], [tblComputer].[ComputerName] as [Computer Name (Computer)],
        [tblComputerShare].[SharePath] as [Share Path (Share)],[tblComputerShare].[ShareName] as [Share Name (Share)],
        dbo.udfBitmaskLookup('ComputerShare', 'ShareType', 1033,
       [tblComputerShare].[ShareType]) as [Share Type (Share)],
       [Type (ACE)] =
       CASE [vwShareACE].[AceType]
       WHEN 0 THEN N'Allow'
       WHEN 1 THEN N'Deny'
       ELSE ''
       END, dbo.udfBitmaskLookup('ShareAce', 'ShareAccessMask', 1033, [vwShareACE].[AccessMask]) as [Permissions (ACE)],
       CASE WHEN LEN([vwACEAccount].[SAMAccountDomain]) = 0 THEN [vwACEAccount].[SAMAccountName]
       ELSE [vwACEAccount].[SAMAccountDomain] + '\' + [vwACEAccount].[SAMAccountName] END as [Account Name (Account)],
       [vwACEAccount].[SAMAccountDomain] as [SAMAccountDomain], [vwACEAccount].[SAMAccountName] as [SAMAccountName]
       FROM
       [tblComputerPhysical] base
       INNER JOIN [tblComputerShare] tblComputerShare
       ON ([base].[PhysicalComputerID] = [tblComputerShare].[PhysicalComputerID] AND
        ([tblComputerShare].[LDM_TOMBSTONED] = 0 OR [tblComputerShare].[LDM_TOMBSTONED] IS NULL))
       LEFT OUTER JOIN [tblACL] tblACL
       ON ([tblComputerShare].[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 [vwShareACE] vwShareACE
       ON ([tblACL].[ACLID] = [vwShareACE].[ACLID])
       LEFT OUTER JOIN [vwACEAccount] vwACEAccount
       ON ([vwShareACE].[AccountID] = [vwACEAccount].[AccountID])
       LEFT OUTER JOIN ([tblDomainUser] tblDomainUser FULL OUTER JOIN [tblDomainUserPart2] tblDomainUser_S0
       ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S0].[DomainUserID])
       FULL OUTER JOIN [tblDomainUserPart3] tblDomainUser_S1
       ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S1].[DomainUserID])
       FULL OUTER JOIN [tblDomainUserExchange] tblDomainUser_S2
       ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S2].[DomainUserID])
       )
       ON ([vwACEAccount].[AccountID] = [tblDomainUser].[AccountID] AND ([tblDomainUser].[LDM_TOMBSTONED] = 0 OR [tblDomainUser].[LDM_TOMBSTONED] IS NULL))
       LEFT OUTER JOIN [tblAccount] tblAccount_1
       ON ([tblDomainUser].[AccountID] = [tblAccount_1].[AccountID] AND ([tblAccount_1].[LDM_TOMBSTONED] = 0 OR [tblAccount_1].[LDM_TOMBSTONED] IS NULL))
       LEFT OUTER JOIN [tblDomainGroup] tblDomainGroup
       ON ([vwACEAccount].[AccountID] = [tblDomainGroup].[AccountID] AND ([tblDomainGroup].[LDM_TOMBSTONED] = 0 OR [tblDomainGroup].[LDM_TOMBSTONED] IS NULL))
       LEFT OUTER JOIN [tblComputerUser] tblComputerUser
       ON ([vwACEAccount].[AccountID] = [tblComputerUser].[AccountID] AND ([tblComputerUser].[LDM_TOMBSTONED] = 0 OR [tblComputerUser].[LDM_TOMBSTONED] IS NULL))
       LEFT OUTER JOIN [tblAccount] tblAccount_2
       ON ([tblComputerUser].[AccountID] = [tblAccount_2].[AccountID] AND ([tblAccount_2].[LDM_TOMBSTONED] = 0 OR [tblAccount_2].[LDM_TOMBSTONED] IS NULL))
       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 OR [tblComputer].[LDM_TOMBSTONED] IS NULL))
       WHERE
       (([tblComputer].[DomainName] IN (@DomainName)) AND
        ([tblComputer].[ComputerName] IN (Select * From @ComputerName))
       AND 1 = 1 AND 1 = 1 AND 1 = 1 AND (base.LDM_TOMBSTONED = 0 OR base.LDM_TOMBSTONED IS NULL))
),
SHARE_PERMISSIONS_EXCLUDE as
(
       SELECT [tblComputer].[DomainName] as [Domain (Computer)], [tblComputer].[ComputerName] as [Computer Name (Computer)],
       [tblComputerShare].[ShareName] as [Share Name (Share)],
       dbo.udfBitmaskLookup('ComputerShare', 'ShareType', 1033, [tblComputerShare].[ShareType]) as [Share Type (Share)],
       [Type (ACE)] =
       CASE [vwShareACE].[AceType]
       WHEN 0 THEN N'Allow'
       WHEN 1 THEN N'Deny'
       ELSE ''
       END, dbo.udfBitmaskLookup('ShareAce', 'ShareAccessMask', 1033, [vwShareACE].[AccessMask]) as [Permissions (ACE)],
       CASE WHEN LEN([vwACEAccount].[SAMAccountDomain]) = 0 THEN [vwACEAccount].[SAMAccountName] ELSE [vwACEAccount].[SAMAccountDomain] + '\' + [vwACEAccount].[SAMAccountName] END as [Account Name (Account)]
       FROM
       [tblComputerPhysical] base
       INNER JOIN [tblComputerShare] tblComputerShare
       ON ([base].[PhysicalComputerID] = [tblComputerShare].[PhysicalComputerID] AND ([tblComputerShare].[LDM_TOMBSTONED] = 0 OR [tblComputerShare].[LDM_TOMBSTONED] IS NULL))
       LEFT OUTER JOIN [tblACL] tblACL
       ON ([tblComputerShare].[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 [vwShareACE] vwShareACE
       ON ([tblACL].[ACLID] = [vwShareACE].[ACLID])
       LEFT OUTER JOIN [vwACEAccount] vwACEAccount
       ON ([vwShareACE].[AccountID] = [vwACEAccount].[AccountID])
       LEFT OUTER JOIN ([tblDomainUser] tblDomainUser FULL OUTER JOIN [tblDomainUserPart2] tblDomainUser_S0
       ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S0].[DomainUserID])
       FULL OUTER JOIN [tblDomainUserPart3] tblDomainUser_S1
       ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S1].[DomainUserID])
       FULL OUTER JOIN [tblDomainUserExchange] tblDomainUser_S2
       ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S2].[DomainUserID])
       )
       ON ([vwACEAccount].[AccountID] = [tblDomainUser].[AccountID] AND ([tblDomainUser].[LDM_TOMBSTONED] = 0 OR [tblDomainUser].[LDM_TOMBSTONED] IS NULL))
       LEFT OUTER JOIN [tblAccount] tblAccount_1
       ON ([tblDomainUser].[AccountID] = [tblAccount_1].[AccountID] AND ([tblAccount_1].[LDM_TOMBSTONED] = 0 OR [tblAccount_1].[LDM_TOMBSTONED] IS NULL))
       LEFT OUTER JOIN [tblDomainGroup] tblDomainGroup
       ON ([vwACEAccount].[AccountID] = [tblDomainGroup].[AccountID] AND ([tblDomainGroup].[LDM_TOMBSTONED] = 0 OR [tblDomainGroup].[LDM_TOMBSTONED] IS NULL))
       LEFT OUTER JOIN [tblComputerUser] tblComputerUser
       ON ([vwACEAccount].[AccountID] = [tblComputerUser].[AccountID] AND ([tblComputerUser].[LDM_TOMBSTONED] = 0 OR [tblComputerUser].[LDM_TOMBSTONED] IS NULL))
       LEFT OUTER JOIN [tblAccount] tblAccount_2
       ON ([tblComputerUser].[AccountID] = [tblAccount_2].[AccountID] AND ([tblAccount_2].[LDM_TOMBSTONED] = 0 OR [tblAccount_2].[LDM_TOMBSTONED] IS NULL))
       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 OR [tblComputer].[LDM_TOMBSTONED] IS NULL))
       WHERE ((CASE WHEN LEN([vwACEAccount].[SAMAccountDomain]) = 0 THEN
        [vwACEAccount].[SAMAccountName] ELSE
        [vwACEAccount].[SAMAccountDomain] + '\' + [vwACEAccount].[SAMAccountName] END) IN
        (select AccountName from dbo.NestedGroupMembersByAccount(@Domain, @Account))
        AND ([tblComputer].[DomainName] IN ((@DomainName))) AND
        ([tblComputer].[ComputerName] IN ((Select * from @ComputerName))) AND
         1 = 1 AND 1 = 1 AND (base.LDM_TOMBSTONED = 0 OR base.LDM_TOMBSTONED IS NULL))

)
SELECT ALL_SHARE_PERMISSIONS.[Domain (Computer)] as [Domain (Computer)],
ALL_SHARE_PERMISSIONS.[Computer Name (Computer)] as [Computer Name (Computer)],
ALL_SHARE_PERMISSIONS.[Share Name (Share)] as [Share Name (Share)],
ALL_SHARE_PERMISSIONS.[Share Type (Share)] as [Share Type (Share)],
ALL_SHARE_PERMISSIONS.[Share Path (Share)] as [Share Path (Share)],
ALL_SHARE_PERMISSIONS.[Account Name (Account)] as [Account Name (Account)],
ALL_SHARE_PERMISSIONS.[Permissions (ACE)] as [Permissions (ACE)]
FROM ALL_SHARE_PERMISSIONS
WHERE ALL_SHARE_PERMISSIONS.[Share Name (Share)] NOT IN (SELECT [Share Name (Share)] from SHARE_PERMISSIONS_EXCLUDE)
ORDER BY [Domain (Computer)] ASC, [Computer Name (Computer)] ASC, [Share Name (Share)] ASC, [Share Type (Share)] ASC

5. Click on "Validate Query" button

6. Click on the “Parameters” tab and add the following required parameters:

     Domain Name, Computer Name and Account

7. Click on the “Layout” tab and create the layout using the layout designer.

Aarti.Malik