-- This report lists all users with permissions on their accounts separate from permission groups - for permission clean-up

Use Polaris

select 
	pu.Name as "User Login"
	,pn.PermissionName
	,crd.ControlRecordName
	--,o.name Org
	

from polaris.PolarisUsers pu
inner join Polaris.PermissionUsers pe
	on pu.PolarisUserID = pe.PolarisUserID
left join polaris.PermissionDefs pd
on pe.PermissionID = pd.PermissionID
left join polaris.PermissionNames pn
on pn.PermissionNameID = pd.PermissionNameID
left join polaris.ControlRecords cr
on pe.ControlRecordID = cr.ControlRecordID
left join polaris.ControlRecordDefs crd
on crd.ControlRecordDefID = cr.ControlRecordDefID
left join Polaris.Organizations o
on cr.OrganizationID = o.OrganizationID
left join polaris.UsersPPPP pppp (nolock)  -- Added to remove suspended users (JT 2018-06-04)
on pppp.PolarisUserID = pu.PolarisUserID AND pppp.AttrID = 659 -- Added (JT 2018-06-04)
where Enabled = 1
/*and pu.Name not like 'Email%' 
and pu.Name not like 'PAC%'
and pu.Name not like '%Self Check%'
and pu.Name not like 'RMB Mediabank'*/
--and pu.OrganizationID not in (1,3,255)
and (pppp.Value != 'Suspended' or pppp.Value is null) -- Added (JT 2018-06-04)

GROUP BY
	pu.Name
	,pn.PermissionName
	,crd.ControlRecordName
	--,o.name
ORDER BY
	pu.Name
	,pn.PermissionName
	,crd.ControlRecordName
	--,o.name