Skip to main content

Security Groups and Members – Quick Query

  1. Last updated
  2. Save as PDF

Occasionally, administrators need a quick way to see all of the users in each of the security groups in TIER®. Opening the Group properties in the treeview to see all of the members, or opening the Member properties to see which groups the user belongs to is one method, but is slow. Instead, this query lists the GroupName and ID and the TierUsers and their UserIDs for all selected groups. You can use this query directly in your SQL lookups and can even create a view for use in lookups and other queries.  This query joins three different system tables: T4W_Users, T4W_Groups, and the joining table, T4W_Members. The results are sorted first by GroupName and then by UserName. SELECT G.OP__ID AS GroupID, G.OP__TIERNAME AS GroupName, U.OP__TIERNAME AS UserName ,U.OP__ID AS TierUserIDFROM T4W_GROUPS G INNER JOIN T4W_MEMBERS M ON G.OP__ID = M.RSIDINNER JOIN T4W_USERS U ON U.OP__ID = M.LSIDWHERE G.OP__ID <>0 --Excludes Everyone GroupAND G.OP__TIERNAME <> 'TERMINATED'--Excludes Terminated GroupORDER BY G.OP__TIERNAME, U.OP__TIERNAMEBy using the WHERE filter, groups can be included or excluded. For example, a security group called “Terminated” can be excluded from a TIER® Lookup Combo of active users, either by GroupName or GroupID. The query above is excluding the ‘Everyone’ group by ID and the ‘Terminated’ Group by name as an example this filtering.  Note: OP__IDs are always negative numbers in the T4W_Groups table and the ‘Everyone’ group has OP__ID = 0.  Best practice for TIER® Security is that users are only ever in ONE group at a time in addition to the Everyone Group. This query can further be modified and used to identify only users in multiple groups by modifying the select statement to only show U.OP__TIERNAME & G.OP__TIERNAME and the following lines at the bottom to group and count multiples.group by U.OP__TIERNAME, G.OP__TIERNAMEHaving count(G.OP__TIERNAME) > 1Managing a small amount of users does allow us to see that Joe Clinician is in the Administrator group AND in the Medical Personnel Group.  This can lead to a conflict in permissions, where the Administrators are allowed to do something, but the Medical Personnel are explicitly denied.  If the user needs to be in both groups, create a new group with the specific permissions for that user.  This will prevent conflicts.(see attached pdf for entire Tidbit and accompanying screen shots)