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

  order by Object, ObjectName

  • No labels