Shares on Windows computers and their share permissions

[Daniel_B]

List of all configured Share folders, including the share permissions set on each folder


Meets all the following criteria:
- Active Asset
- Windows Asset
- Network Share folders configured

Sorted on:
- Asset name

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Description,
  tsysOS.OSname,
  tblSharesUni.Name,
  tblSharesUni.Path,
  tsysOS.Image As icon,
  tblSharePermissions.trustee As [for user/group],
  Case When tblSharePermissions.readAccess = 1 Then 'y' Else 'n' End As [Read],
  Case When tblSharePermissions.writeAccess = 1 Then 'y' Else 'n' End As Write,
  Case When tblSharePermissions.fullAccess = 1 Then 'y' Else 'n' End As [Full],
  Case When tblSharePermissions.denyAccess = 1 Then 'y' Else 'n' End As Denied,
  tblShares.Lastchanged
From tblShares
  Inner Join tblAssets On tblShares.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSharesUni On tblShares.ShareUniqueID =
    tblSharesUni.ShareUniqueID
  Inner Join tblSharePermissions
    On tblShares.ShareID = tblSharePermissions.ShareID
Where tblAssetCustom.State = 1 And tblSharesUni.Type = 0
Order By tblAssets.AssetName

[Rami_Ferwana]

Great Support Thank you

[Rami_Ferwana]

Can you add the display name ,login name, department, title and manager name for [for user/group]?

I need a report with shared folder permissions contain users from different departments

[Susan_A]

Rami Ferwana wrote:
Can you add the display name ,login name, department, title and manager name for [for user/group]?

Please try the report below.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Description,
  tsysOS.OSname,
  tblSharesUni.Name,
  tblSharesUni.Path,
  tsysOS.Image As icon,
  tblSharePermissions.trustee As [for user/group],
  tblADusers.Username,
  tblADusers.Userdomain,
  tblADusers.Displayname,
  tblADusers.Department,
  tblADusers.Title,
  tblADObjects.sAMAccountName As Manager,
  Case When tblSharePermissions.readAccess = 1 Then 'y' Else 'n' End As [Read],
  Case When tblSharePermissions.writeAccess = 1 Then 'y' Else 'n' End As Write,
  Case When tblSharePermissions.fullAccess = 1 Then 'y' Else 'n' End As [Full],
  Case When tblSharePermissions.denyAccess = 1 Then 'y' Else 'n' End As Denied,
  tblShares.Lastchanged
From tblShares
  Inner Join tblAssets On tblShares.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSharesUni On tblShares.ShareUniqueID =
    tblSharesUni.ShareUniqueID
  Inner Join tblSharePermissions
    On tblShares.ShareID = tblSharePermissions.ShareID
  Left Join tblADusers On tblADusers.Userdomain + '\' + tblADusers.Username =
    tblSharePermissions.trustee
  Left Join tblADObjects On tblADObjects.ADObjectID =
    tblADusers.ManagerADObjectId
Where tblAssetCustom.State = 1 And tblSharesUni.Type = 0
Order By tblAssets.AssetName

[Piyush_Rawat]

Susan.A wrote:

Rami Ferwana wrote:
Can you add the display name ,login name, department, title and manager name for [for user/group]?

Please try the report below.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Description,
  tsysOS.OSname,
  tblSharesUni.Name,
  tblSharesUni.Path,
  tsysOS.Image As icon,
  tblSharePermissions.trustee As [for user/group],
  tblADusers.Username,
  tblADusers.Userdomain,
  tblADusers.Displayname,
  tblADusers.Department,
  tblADusers.Title,
  tblADObjects.sAMAccountName As Manager,
  Case When tblSharePermissions.readAccess = 1 Then 'y' Else 'n' End As [Read],
  Case When tblSharePermissions.writeAccess = 1 Then 'y' Else 'n' End As Write,
  Case When tblSharePermissions.fullAccess = 1 Then 'y' Else 'n' End As [Full],
  Case When tblSharePermissions.denyAccess = 1 Then 'y' Else 'n' End As Denied,
  tblShares.Lastchanged
From tblShares
  Inner Join tblAssets On tblShares.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSharesUni On tblShares.ShareUniqueID =
    tblSharesUni.ShareUniqueID
  Inner Join tblSharePermissions
    On tblShares.ShareID = tblSharePermissions.ShareID
  Left Join tblADusers On tblADusers.Userdomain + '\' + tblADusers.Username =
    tblSharePermissions.trustee
  Left Join tblADObjects On tblADObjects.ADObjectID =
    tblADusers.ManagerADObjectId
Where tblAssetCustom.State = 1 And tblSharesUni.Type = 0
Order By tblAssets.AssetName

Is there any way that the name of the owner/user who created/altered the "share" is included in the report ?

[Bradford]

It's working now. Thanks so much guys!

[Daniel_B]

I've modified the report in the first post again. It still had a syntax mistake in the alias names.

[Bradford]

Thanks Daniel, this looks like exactly what I need...

Unfortunately though when I paste in the code and save I get this error:

"Error while saving: "There was an error parsing the query. [ Token line number = 1,Token line offset = 299,Token in error = Read ]""

I just copied it manually (vs copy and pasting) and I am still getting the same error.

Please help, I do not know enough about SQL to understand what I am doing at this point, or what type of error this is.

Thanks!

[Daniel_B]

Thanks for the reminder. We have corrected it in the report above.

[sukaitsu]

Great report! I noticed there needs to be ' ' surrounding the End As aliases in your case statement.

Case When tblSharePermissions.readAccess = 1 Then 'y' Else 'n' End As 'Read',
Case When tblSharePermissions.writeAccess = 1 Then 'y' Else 'n' End As 'Write',
Case When tblSharePermissions.fullAccess = 1 Then 'y' Else 'n' End As 'Full',
Case When tblSharePermissions.denyAccess = 1 Then 'y' Else 'n' End As 'Denied',

Thank you,

Jeffrey