Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Code Block
languagesql
titleSQL
collapselinenumberstrue
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

 

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%')

...