Page History

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 and (t2.loc.value('(identity/@name)[1]', 'nvarchar(max)') like '%sefa%' or pe.DisplayName like '%sefa%')
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 and (t2.loc.value('(identity/@name)[1]', 'nvarchar(max)') like '%sefa%' or pe.DisplayName like '%sefa%')
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 and (t2.loc.value('(identity/@name)[1]', 'nvarchar(max)') like '%sefa%' or pe.DisplayName like '%sefa%')
  order by Object, ObjectName