SQL query - To get list of duty and Privilege of any role

select


a.aotname, -- Role aot name

a.name, -- Role name

c.AOTNAME as taskname, -- Duty or privilige name

CASE when c.TYPE=1 then 'duty' else 'privilage' end as tasktype,

e.AOTNAME as privilagename




from


securityrole a

inner join SECURITYROLETASKGRANT b

on b.SECURITYROLE=a.RECID

inner join SECURITYTASK c

on c.RECID=b.SECURITYTASK

left join SECURITYSUBtask d

on d.SECURITYTASK=b.SECURITYTASK

left join securitytask e

on e.recid=d.securitysubtask

left join SECURITYTASKENTRYPOINT f

on f.SECURITYTASK=d.SECURITYSUBTASK or f.SECURITYTASK=b.SECURITYTASK

group by a.aotname, -- Role aot name

a.name, -- Role name

c.AOTNAME,

c.TYPE,e.AOTNAME