SQL
SELECT 'Library' as 'Object',l.Name 'ObjectName', t2.loc.value('@type','nvarchar(10)') as yetki, t2.loc.value('(identity/@name)[1]', 'nvarchar(max)') as idendityName,t2.loc.value('(identity/@type)[1]', 'nvarchar(max)') as idendityType, pe.DisplayName
FROM (select Id,cast(cast(Graph as NTEXT) AS XML) as Graph from wflaclLibrary) a cross apply
Graph.nodes('/permissions/permissionEntry') as t2(loc)
left join wflLibraries l on a.Id=l.Id
left join schGroupMember gm on t2.loc.value('(identity/@key)[1]', 'uniqueidentifier')=gm.[Group] and t2.loc.value('(identity/@type)[1]', 'nvarchar(max)')='Group'
left join schOrganizationalUnitMember ou on ou.OrganizationalUnit=t2.loc.value('(identity/@key)[1]', 'uniqueidentifier') and t2.loc.value('(identity/@type)[1]', 'nvarchar(max)')='OrganizationUnit'
left join schPerson pe on (gm.Member=pe.Id or ou.Employee=pe.Id )
where l.Name is not null
UNION
SELECT
'Process' as 'Object', p.Name 'ObjectName',t2.loc.value('@type','nvarchar(10)') as yetki, t2.loc.value('(identity/@name)[1]', 'nvarchar(max)') as idendityName, t2.loc.value('(identity/@type)[1]', 'nvarchar(max)') as idendityType, pe.DisplayName
FROM (select Id,cast(cast(Graph as NTEXT) AS XML) as Graph from wflaclProcess) a cross apply
Graph.nodes('/permissions/permissionEntry') as t2(loc)
left join wflProcesses p on a.Id=p.Id
left join schGroupMember gm on t2.loc.value('(identity/@key)[1]', 'uniqueidentifier')=gm.[Group] and t2.loc.value('(identity/@type)[1]', 'nvarchar(max)')='Group'
left join schOrganizationalUnitMember ou on ou.OrganizationalUnit=t2.loc.value('(identity/@key)[1]', 'uniqueidentifier') and t2.loc.value('(identity/@type)[1]', 'nvarchar(max)')='OrganizationUnit'
left join schPerson pe on (gm.Member=pe.Id or ou.Employee=pe.Id )
where p.Name is not null
UNION
SELECT
'Reports' as 'Object',r.Name 'ObjectName', t2.loc.value('@type','nvarchar(10)') as yetki,t2.loc.value('(identity/@name)[1]', 'nvarchar(max)') as idendityName, t2.loc.value('(identity/@type)[1]', 'nvarchar(max)') as idendityType, pe.DisplayName
FROM (select Id,cast(cast(Graph as NTEXT) AS XML) as Graph from wflaclReport) a cross apply
Graph.nodes('/permissions/permissionEntry') as t2(loc)
left join wflReports r on a.Id=r.Id
left join schGroupMember gm on t2.loc.value('(identity/@key)[1]', 'uniqueidentifier')=gm.[Group] and t2.loc.value('(identity/@type)[1]', 'nvarchar(max)')='Group'
left join schOrganizationalUnitMember ou on ou.OrganizationalUnit=t2.loc.value('(identity/@key)[1]', 'uniqueidentifier') and t2.loc.value('(identity/@type)[1]', 'nvarchar(max)')='OrganizationUnit'
left join schPerson pe on (gm.Member=pe.Id or ou.Employee=pe.Id )
where r.Name is not null
order by Object, ObjectName