Can we query the security module tables?

Hi Ashley,

I got this code from someone on the network quite some time ago, sorry I don't know who I can give the credit to but it has been a great help to me. I save the results and use them in Excel with filters or a pivot table to get the information I am looking for.  

Use impresario

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

Ashley here is the code again with the tables added in that Michael suggested to get the user names.  I also added a few where clauses you can turn on or off if you are looking for a particular user group or object. 

Use impresario
select ug.UG_id
, ug.UG_name
, ao.object_id object_id
, ao.name object
, ao.object_type
, us.lname
, us.fname
, 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
left outer join tx_user_group gr on gr.UG_id = ug.ug_id
left outer join t_metuser us on us.userid = gr.userid
--where UG_Name = 'Type User Group Name here' --use this to look for a particular user group by it's full name
--where ao.name like '%Type object name here%' --this is the object field use this to find a particular object by any part of its name
order by ug.UG_name , ao.name

Ashley here is the code again with the tables added in that Michael suggested to get the user names.  I also added a few where clauses you can turn on or off if you are looking for a particular user group or object. 

Use impresario
select ug.UG_id
, ug.UG_name
, ao.object_id object_id
, ao.name object
, ao.object_type
, us.lname
, us.fname
, 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
left outer join tx_user_group gr on gr.UG_id = ug.ug_id
left outer join t_metuser us on us.userid = gr.userid
--where UG_Name = 'Type User Group Name here' --use this to look for a particular user group by it's full name
--where ao.name like '%Type object name here%' --this is the object field use this to find a particular object by any part of its name
order by ug.UG_name , ao.name

One more thing. I realized this includes users even if they are inactive.  To fix that you will want to add in the where clause us.inactive = 'N'   If you don't use any of my commented out where clauses then you would put it just above the order by like this:   where us.inactive ='N'

If you are going to use one of the commented out where clauses then delete the two dashes before the one you want to use, change the text to what you are looking for and after the where clause type this:  and us.inactive ='N'  

This was a great exercise for me and I quickly found a user who was still active and should not have been.  I may have to build this into a report. 

Terry,

This was soo helpful! Thank you. Problem solved.

Ashley

So glad I could help Ashley