Companies With Security Roles
The purpose of this view example is to help churches identify any Company Users records with Security Roles in their systems (which are no longer recommended) and to display relevant information related to those records, like last login date.
Difficulty: Advanced
Purpose: To identify Company User records that contain Platform and/or other Security Roles.
Page: Users
This View shows all Company Users records with Security Roles counts to all applications as well as the Platform specifically.
Columns (View Field List)
This field shows the following columns: User ID, Company Name, Total Role Count, Platform Role Count, Most Recent Login, and Total Logins.
dp_Users.[User_ID] AS [User ID], Contact_ID_Table.[Company_Name] AS [Company Name], (SELECT COUNT(*) FROM dp_User_Roles UR WHERE UR.[User_ID] = dp_Users.[User_ID]) AS [Total Role Count], (SELECT COUNT(*) FROM dp_User_Roles UR WHERE UR.[User_ID] = dp_Users.[User_ID] AND (EXISTS(SELECT 1 FROM dp_Role_Pages RPWHERE RP.[Role_ID] = UR.[Role_ID]AND RP.[Access_Level] IS NOT NULL)OR EXISTS(SELECT 1 FROM dp_Role_Sub_Pages RSPWHERE RSP.[Role_ID] = UR.[Role_ID]AND RSP.[Access_Level] IS NOT NULL))) AS [Platform Role Count], (SELECT TOP 1 AL.[Date_Time] FROM dp_Authentication_Log ALWHERE AL.[User_ID] = dp_Users.[User_ID]ORDER BY AL.[Date_Time] DESC) AS [Most Recent Login], (SELECT COUNT(*) FROM dp_Authentication_Log ALWHERE AL.[User_ID] = dp_Users.[User_ID]) AS [Total Logins]Criteria (View Clause)
This criteria limits the results to only Company User records with Security Roles.
Contact_ID_Table.[Company] = 1AND (SELECT COUNT(*) FROM dp_User_Roles URWHERE UR.[User_ID] = dp_Users.[User_ID]AND (EXISTS(SELECT 1 FROM dp_Role_Pages RPWHERE RP.[Role_ID] = UR.[Role_ID]AND RP.[Access_Level] IS NOT NULL)OR EXISTS(SELECT 1 FROM dp_Role_Sub_Pages RSPWHERE RSP.[Role_ID] = UR.[Role_ID]AND RSP.[Access_Level] IS NOT NULL))) > 0CopiedTechniques
- EXISTS
- COUNT
- SELECT DISTINCT