Hi,
Is there a report that lists the security permissions and access levels for a user group? Ultimately, I want to look at the levels across different user groups. I thought this came up in a forum discussion a while back, but I haven't been able to find the thread. Thanks for your help! Sue
Hi Susan, Yes, this was discussed in a recent forum and Justin Cheek shared the Excel spreadsheet he uses to keep track of security. Since then I found some code in my saved documents from way back that can be used to query security permission/levels assigned. There are two versions. The first one shows only rights assigned and does not include the Group Id. The second one shows all security objects and whether they are assigned or not and includes the Group Id as well.
--First Version***********************************************
Use impresario
select distinct b.UG_name, c.name, c.description, c.object_id, c.object_type,
ISNULL(a.adding,'') as 'adding', ISNULL(a.deleting,'') as 'deleting',
ISNULL(a.editing,'') as 'editing', ISNULL(a.viewing,'') as 'viewing',
ISNULL(d.short_desc,'') as 'constituency'
from TX_SECURITY_RIGHTS a
join t_metusergroup b on a.ug_id=b.ug_id
join t_app_objects c on a.object_id=c.object_id
left outer join tr_constituency d on a.constituency=d.id
order by b.UG_name, c.object_id, constituency, c.name, c.object_type, adding,
deleting, editing, viewing, c.description
--Second Version***************************************************************
select ug.UG_id
, ug.UG_name
, ao.object_id object_id
, ao.name object
, ao.object_type
, isnull(sr.id, 0) rights_id
, ISNULL(sr.adding,'') as 'adding'
, ISNULL(sr.deleting,'') as 'deleting'
, ISNULL(sr.editing,'') as 'editing'
, ISNULL(sr.viewing,'') as 'viewing'
,ISNULL(d.short_desc,'') as 'constituency'
from T_METUSERGROUP ug
cross join T_APP_OBJECTS ao
left outer join TX_SECURITY_RIGHTS sr on sr.UG_id = ug.UG_id and sr.object_id = ao.object_id
left outer join tr_constituency d on sr.constituency=d.id
order by ug.UG_name , ao.name