Page tree
Skip to end of metadata
Go to start of metadata
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



  • No labels