Everyone knows how difficult it is to find all the places where a staff person is a leader of a group, has connections, the main contact of an upcoming event, etc. This is an in depth SQL dynamic data report, a good knowledge of SQL is required. that pulls data from various tables that pertain to different responsibilities a staff person may have had that need to be removed. Some of the SQL statements may need to be adjusted based on your Rock implementation. 

Each panel lists the Name of the Group (i.e. Security Groups) and the list of the items (i.e. RSR - Rock Administration). Next to each item is either an pencil to edit the item directly, or a sync button because the group may be based on a dataview and the dataview can be edited.

PersonSecurity.png

Page

On the page are 2 blocks a Workflow Entry Block and a Dynamic Data Block. The Workflow is a simple Person Picker that allows you to select any person profile and run the page based on that person. The Dynamic Data Block is comprised of 12 different SQL queries all using PersonGuid as the basis for finding their roles.

Workflow Entry Block

This block is a simple workflow that allows you to find a person, click filter and the page redirects to itself with the PersonGuid in the page parameter that can be read by the dynamic data block. We use this style of filtering for many of our pages. It is a lot cleaner and easier for users to make the correct choices.

PersonPicker.png

Dynamic Data Block

This is the meat and potatoes of the page.

The Query section has 12 separate queries. Each one is labeled with what is being queried so you can decide if you need to change it or remove it from your query.

IF @Person != ''
-- 1. Security Groups
select g.Id, g.Name as 'Group', g.IsActive, g.IsArchived, gs.SyncDataViewId
from [Group] g
inner join GroupMember gm on g.id = gm.GroupId and gm.IsArchived != 1
inner join PersonAlias pa on pa.Personid = gm.PersonId and pa.Guid = @person
inner join GroupType gt on g.GroupTypeid = gt.Id
left join GroupSync gs on g.Id = gs.GroupId
where g.IsSecurityRole = 1
order by g.Name;

IF @Person != ''
-- 2. Approval Groups | Used for Room Reservation Plugin
select g.Id, g.Name as 'Group', g.IsActive, g.IsArchived, gs.SyncDataViewId
from [Group] g
inner join GroupMember gm on g.id = gm.GroupId and gm.IsArchived != 1
inner join PersonAlias pa on pa.Personid = gm.PersonId and pa.Guid = @person
inner join GroupType gt on g.GroupTypeid = gt.Id
left join GroupSync gs on g.Id = gs.GroupId
where g.GroupTypeId = 34
order by gt.Name;

IF @person != ''
-- 3. Connector Groups | The person is in a connector group and could be assigned
select g.Id, g.Name as 'Group', g.IsActive, g.IsArchived, gs.SyncDataViewId
from [Group] g
inner join GroupMember gm on g.id = gm.GroupId and gm.IsArchived != 1
inner join PersonAlias pa on pa.Personid = gm.PersonId and pa.Guid = @person
inner join GroupType gt on g.GroupTypeid = gt.Id
left join GroupSync gs on g.Id = gs.GroupId
where g.GroupTypeId = 55
order by gt.Name;

IF @person != ''
-- 4. Group Leadership
select g.Id as 'GroupId', g.Name as 'GroupName', g.IsActive, g.IsArchived
from [Group] g 
inner join GroupMember gm on g.Id = gm.GroupId and gm.IsArchived != 1
inner join PersonAlias pa on gm.PersonId = pa.PersonId and pa.Guid = @person
inner join GroupTypeRole gtr on gm.GroupRoleId = gtr.Id and gtr.IsLeader = 1;

IF @person != ''
-- 5. Default Connector | The person is the default connector for a connection
select co.Id, co.ConnectionTypeId, co.Name, c.Name as 'CampusName'
from ConnectionOpportunity co 
inner join ConnectionOpportunityCampus coc on co.Id = coc.ConnectionOpportunityId
inner join PersonAlias pa on coc.DefaultConnectorPersonAliasId = pa.Id and pa.Guid = @person
inner join Campus c on coc.CampusId = c.Id
order by co.Name, c.Name;

IF @person != ''
-- 6. Active Connections | The person has active connections. They should be re-assigned to other people
select cr.Id, co.Name as 'ConnectionOpportunity', p.NickName+' '+p.LastName as 'PersonName' from ConnectionRequest cr 
inner join PersonAlias pa on cr.ConnectorPersonAliasId = pa.Id and cr.ConnectionState = 0 and pa.Guid = @person
inner join ConnectionOpportunity co on cr.ConnectionOpportunityId = co.Id
inner join PersonAlias pa2 on cr.PersonAliasId = pa2.Id
inner join Person p on pa2.PersonId = p.Id
order by co.Name,cr.Id;

IF @person != ''
-- 7. Active Event Contact | This person is an event contact and may be displayed on the website as such.
select eio.Id, ei.Name
from EventItem ei
inner join EventItemOccurrence eio on ei.Id = eio.EventItemId
inner join Schedule s on eio.ScheduleId = s.Id and s.EffectiveEndDate > getdate()
inner join PersonAlias pa on eio.ContactPersonAliasId = pa.Id and pa.Guid = @person
where ei.IsActive = 1;

IF @person != ''
-- 8. Active Registration Contact | This person is a registration contact and may be contacted about registrations.
select ri.Id, ri.Name
from RegistrationInstance as ri
inner join Registrationtemplate rt on ri.RegistrationTemplateId = rt.Id
inner join PersonAlias pa on ri.ContactPersonAliasId = pa.Id and pa.Guid = @person
where ri.IsActive = 1 and ri.EndDateTime > getdate();

IF @Person != ''
-- 9. Org Chart
select g.Id, g.Name as 'Group', g.IsActive, g.IsArchived, gs.SyncDataViewId
from [Group] g
inner join GroupMember gm on g.id = gm.GroupId and gm.IsArchived != 1
inner join PersonAlias pa on pa.Personid = gm.PersonId and pa.Guid = @person
inner join GroupType gt on g.GroupTypeid = gt.Id
left join GroupSync gs on g.Id = gs.GroupId
where g.GroupTypeId = 28
order by g.Name;

IF @Person != ''
-- 10. Miscellaneous Security Settings | Various permissions throughout Rock. As I have identified the type of Entity I have joined in the entity table to get enough details to be able to remove the person's permissions
select DISTINCT Page.InternalName as PageName,g.Name as GroupName, a.EntityTypeId as EntityType, et.FriendlyName as EntityName, a.EntityId as EntityId, rt.Name as RegName, dv.Name as Dataview, r.Name as Report, b.Name as Block, b.PageId, s.Name as Schedule
from Auth a 
Inner Join PersonAlias pa on a.PersonAliasId = pa.Id and pa.Guid = @person
inner join EntityType et on a.EntityTypeId = et.Id
left join Page on a.EntityId = page.Id and et.Id = 2
left join [Group] g on a.EntityId = g.Id and et.Id = 16
left join RegistrationTemplate rt on a.EntityId = rt.Id and et.Id = 234
left join DataView dv on a.EntityId = dv.Id and et.Id = 34
left join Report r on a.EntityId = r.Id and et.Id = 107
left join Block b on a.EntityId = b.Id and et.Id = 9
left join schedule s on a.EntityId = s.Id and et.Id = 54
order by et.FriendlyName;

IF @Person != ''
-- 11. SMS From Values | This person is attached to SMS numbers that may receive SMS messages.
select dv.[Value], dv.[Description]
from DefinedValue dv
inner join AttributeValue av on dv.Id = av.EntityId and av.AttributeId = 949 and av.Value = @person
where DefinedTypeId=32;

IF @Person != ''
-- 12. Reservations | Used with the Room Reservation Plugin to identify people who have reserved rooms
select r.Id, r.Name
from _com_centralaz_RoomManagement_Reservation r
inner join PersonAlias pa on r.EventContactPersonAliasId = pa.Id and pa.Guid = @person

The Formatted Output iterates through each sql statement in the Query Section and displays the appropriate name and link for editing.

{% assign Person2 = 'Global' | PageParameter:'Person' | PersonByAliasGuid %}
  {% assign Set = 'Global' | PageParameter:'Person' %}
  {%if Set != '' %}
      <h2>{{Person2.FullName}}</h2>
      {%if Person2.Email contains '@ccvsocal.com' %}
          <h3><a href="/Person/{{ Person2.Id}}" class="btn btn-default"><i class="fa fa-pencil"></i></a> Email: {{Person2.Email }}</h3>
      {% endif %}
      <br>
      {% assign numtables = 0 %}
      <div class="row">

        {% comment %} SQL Table 1 - Security Groups{% endcomment %}
          {% assign results = table1.rows | Size %}
          {% if results != 0 %}
              {% assign numtables = numtables | Plus:1%}
              <div class="col-md-4">
                  {[ panel title:'Security Groups']}
                  {% for row in table1.rows %}
                      {% if row.SyncDataViewId <> '' and row.SyncDataViewId %}
                          {% capture editurl %}/page/145?DataViewId={{row.SyncDataViewId}}{% endcapture %}
                      {% else %}
                          {% capture editurl %}/page/113?GroupId={{row.Id}}{% endcapture %}
                      {% endif %}
                      <b {%if row.IsActive !=1 or row.IsArchived !=0 %}style="color:Lightgrey;"{% endif %}><a href="{{ editurl }}" class="btn-xs btn-default">{% if row.SyncDataViewId <> '' and row.SyncDataViewId %}<i class="fa fa-sync-alt"></i>{%else%}<i class="fa fa-pencil"></i>{%endif%}</a> {{ row.Group }}</b><br>
                  {% endfor %}
                  {[ endpanel ]}
              </div>
          {% endif %}

        {% comment %}SQL Table 9 - Orgranization Groups{% endcomment %}
          {% assign results = table9.rows | Size %}
          {% if results != 0 %}
              {% assign numtables = numtables | Plus:1%}
              <div class="col-md-4">
                  {[ panel title:'Org Chart']}
                  {% for row in table9.rows %}
                      {% if row.SyncDataViewId <> '' and row.SyncDataViewId %}
                          {% capture editurl %}/page/145?DataViewId={{row.SyncDataViewId}}{% endcapture %}
                      {% else %}
                          {% capture editurl %}/page/113?GroupId={{row.Id}}{% endcapture %}
                      {% endif %}
                      <b {%if row.IsActive !=1 or row.IsArchived !=0 %}style="color:Lightgrey;"{% endif %}><a href="{{ editurl }}" class="btn-xs btn-default">{% if row.SyncDataViewId <> '' and row.SyncDataViewId %}<i class="fa fa-sync-alt"></i>{%else%}<i class="fa fa-pencil"></i>{%endif%}</a> {{ row.Group }}</b><br>
                  {% endfor %}
                  {[ endpanel ]}
              </div>
          {% endif %}

        {% comment %}SQL Table 4 - Group Leadership{% endcomment %}
          {% assign results = table4.rows | Size %}
          {% if results != 0 %}
              {% assign numtables = numtables | Plus:1%}
              <div class="col-md-4">
                  {[ panel title:'Group Leadership']}
                  {% for row in table4.rows %}
                      <b {%if row.IsActive !=1 or row.IsArchived !=0 %}style="color:Lightgrey;"{% endif %}><a href="/page/113?GroupId={{row.GroupId}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.GroupName }}</b><br>
                  {% endfor %}
                  {[ endpanel ]}
              </div>
          {% endif %}
          {% assign rem = numtables | Modulo:3 %}
          {% if rem == 0 %}
              </div> 
              <div class="row">
          {% endif %}

        {% comment %}SQL Table 2 - Approval Groups | Used for Room Reservation Plugin{% endcomment %}
          {% assign results = table2.rows | Size %}
          {% if results != 0 %}
              {% assign numtables = numtables | Plus:1%}
              <div class="col-md-4">
                  {[ panel title:'Approval Groups']}
                  {% for row in table2.rows %}
                      {% if row.SyncDataViewId <> '' and row.SyncDataViewId %}
                          {% capture editurl %}/page/145?DataViewId={{row.SyncDataViewId}}{% endcapture %}
                      {% else %}
                          {% capture editurl %}/page/113?GroupId={{row.Id}}{% endcapture %}
                      {% endif %}
                      <b {%if row.IsActive !=1 or row.IsArchived !=0 %}style="color:Lightgrey;"{% endif %}><a href="/page/113?GroupId={{row.Id}}" class="btn-xs btn-default">{% if row.SyncDataViewId <> '' and row.SyncDataViewId %}<i class="fa fa-sync-alt"></i>{%else%}<i class="fa fa-pencil"></i>{%endif%}</a> {{ row.Group }}</b><br>
                  {% endfor %}
                  {[ endpanel ]}
              </div>
          {% endif %}
          {% assign rem = numtables | Modulo:3 %}
          {% if rem == 0 %}
              </div> 
              <div class="row">
          {% endif %}

        {% comment %}SQL Table 5 - Connection Opportunity Default | The person is the default connector for a connection{% endcomment %}
          {% assign results = table5.rows | Size %}
          {% if results != 0 %}
              {% assign numtables = numtables | Plus:1%}
              <div class="col-md-4">
                  {[ panel title:'Connection Opportunity Default']}
                  {% for row in table5.rows %}
                      <b><a href="/page/411?ConnectionOpportunityId={{row.Id}}&ConnectionTypeId={{row.ConnectionTypeId}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.Name }} | {{ row.CampusName }}</b><br>
                  {% endfor %}
                  {[ endpanel ]}
              </div>
          {% endif %}
          {% assign rem = numtables | Modulo:3 %}
          {% if rem == 0 %}
              </div> 
              <div class="row">
          {% endif %}

        {% comment %}SQL Table 3 - Connector Groups | The person is in a connector group and could be assigned{% endcomment %}
          {% assign results = table3.rows | Size %}
          {% if results != 0 %}
              {% assign numtables = numtables | Plus:1%}
              <div class="col-md-4">
                  {[ panel title:'Connector Groups']}
                  {% for row in table3.rows %}
                      {% if row.SyncDataViewId <> '' and row.SyncDataViewId %}
                          {% capture editurl %}/page/145?DataViewId={{row.SyncDataViewId}}{% endcapture %}
                      {% else %}
                          {% capture editurl %}/page/113?GroupId={{row.Id}}{% endcapture %}
                      {% endif %}
                      <b {%if row.IsActive !=1 or row.IsArchived !=0 %}style="color:Lightgrey;"{% endif %}><a href="/page/113?GroupId={{row.Id}}" class="btn-xs btn-default">{% if row.SyncDataViewId <> '' and row.SyncDataViewId %}<i class="fa fa-sync-alt"></i>{%else%}<i class="fa fa-pencil"></i>{%endif%}</a> {{ row.Group }}</b><br>
                  {% endfor %}
                  {[ endpanel ]}
              </div>
          {% endif %}
          {% assign rem = numtables | Modulo:3 %}
          {% if rem == 0 %}
              </div> 
              <div class="row">
          {% endif %}

        {% comment %}SQL Table 9 - Active Connections | The person has active connections. They should be re-assigned to other people.{% endcomment %}
          {% assign results = table6.rows | Size %}
          {% if results != 0 %}
              {% assign numtables = numtables | Plus:1%}
              <div class="col-md-4">
                  {[ panel title:'Active Connections']}
                  {% for row in table6.rows %}
                      <b><a href="/page/408?ConnectionRequestId={{row.Id}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.ConnectionOpportunity }} | {{ row.PersonName }}</b><br>
                  {% endfor %}
                  {[ endpanel ]}
              </div>
          {% endif %}
          {% assign rem = numtables | Modulo:3 %}
          {% if rem == 0 %}
              </div> 
              <div class="row">
          {% endif %}

        {% comment %}SQL Table 7 - Event Contact | This person is an event contact and may be displayed on the website as such.{% endcomment %}
          {% assign results = table7.rows | Size %}
          {% if results != 0 %}
              {% assign numtables = numtables | Plus:1%}
              <div class="col-md-4">
                  {[ panel title:'Event Contact']}
                  {% for row in table7.rows %}
                      <b><a href="/page/402?EventItemOccurrenceId={{row.Id}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.Name }}</b><br>
                  {% endfor %}
                  {[ endpanel ]}
              </div>
          {% endif %}
          {% assign rem = numtables | Modulo:3 %}
          {% if rem == 0 %}
              </div> 
              <div class="row">
          {% endif %}

        {% comment %}SQL Table 8 - Registration Contact | This person is a registration contact and may be contacted about registrations.{% endcomment %}
          {% assign results = table8.rows | Size %}
          {% if results != 0 %}
              {% assign numtables = numtables | Plus:1%}
              <div class="col-md-4">
                  {[ panel title:'Registration Contact']}
                  {% for row in table8.rows %}
                      <b><a href="/RegistrationInstance/{{row.Id}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.Name }}</b><br>
                  {% endfor %}
                  {[ endpanel ]}
              </div>
          {% endif %}
          {% assign rem = numtables | Modulo:3 %}
          {% if rem == 0 %}
              </div> 
              <div class="row">
          {% endif %}

        {% comment %}SQL Table 12 - Room Reservations | Used with the Room Reservation Plugin to identify people who have reserved rooms{% endcomment %}
          {% assign results = table12.rows | Size %}
          {% if results != 0 %}
              {% assign numtables = numtables | Plus:1%}
              <div class="col-md-4">
                  {[ panel title:'Room Reservations']}
                  {% for row in table12.rows %}
                      <b><a href="/ReservationDetail?ReservationId={{row.Id}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.Name }}</b><br>
                  {% endfor %}
                  {[ endpanel ]}
              </div>
          {% endif %}
          {% assign rem = numtables | Modulo:3 %}
          {% if rem == 0 %}
              </div> 
              <div class="row">
          {% endif %}

        {% comment %}SQL Table 11 - SMS From Values | This person is attached to SMS numbers that may receive SMS messages.{% endcomment %}
          {% assign results = table11.rows | Size %}
          {% if results != 0 %}
              {% assign numtables = numtables | Plus:1%}
              <div class="col-md-4">
                  {[ panel title:'SMS From Values']}
                  {% for row in table11.rows %}
                      <b><a href="/page/327" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.Value }} | {{ row.Description }}</b><br>
                  {% endfor %}
                  {[ endpanel ]}
              </div>
          {% endif %}
          {% assign rem = numtables | Modulo:3 %}
          {% if rem == 0 %}
              </div> 
              <div class="row">
          {% endif %}

        {% comment %}SQL Table 10 - Miscellaneous Authorizations | Various permissions throughout Rock. As I have identified the type of Entity I have joined in the entity table to get enough details to be able to remove the person's permissions{% endcomment %}
          {% assign results = table10.rows | Size %}
          {% if results != 0 %}
              {% assign numtables = numtables | Plus:1%}
              <div class="col-md-4">
                  {[ panel title:'Miscellaneous Authorizations']}
                  {% for row in table10.rows %}
                      {% case row.EntityType %}
                      {% when '2' %}
                          <b><a href="/page/103?Page={{row.EntityId}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.PageName }} ({{row.EntityName}})</b><br>
                      {% when '16' %}
                          <b><a href="/page/113?GroupId={{row.EntityId}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.GroupName }} ({{row.EntityName}})</b><br>
                      {% when '234' %}
                          <b><a href="/RegistrationInstance/{{row.EntityId}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.RegName }} ({{row.EntityName}})</b><br>
                      {% when '34' %}
                          <b><a href="/page/145?DataViewId={{row.EntityId}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.Dataview }} ({{row.EntityName}})</b><br>
                      {% when '107' %}
                          <b><a href="/page/149?ReportId={{row.EntityId}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.Report }} ({{row.EntityName}})</b><br>
                      {% when '9' %}
                          <b><a href="/page/103?Page={{row.PageId}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.Block }} ({{row.EntityName}})</b><br>
                      {% when '54' %}
                          <b><a href="/Schedules?ScheduleId={{row.EntityId}}" class="btn-xs btn-default"><i class="fa fa-pencil"></i></a> {{ row.Schedule }} ({{row.EntityName}})</b><br>
                      {% else %}
                          <b>{{ row.EntityName }}({{row.EntityType}}) | Id:{{row.EntityId}}</b><br>
                      {% endcase %}
                  {% endfor %}
                  {[ endpanel ]}
              </div>
          {% endif %}
      </div> 
  {% else %}
      <h2>Select a Person Above</h2>
  {% endif %}

Be sure to add Person= to the Parameter field of the Dynamic Data Block.

The bottom one named Miscellaneous Authorizations is a list of one-off authorizations that may have been made for that person to view/edit or something else that isn't tied to a security role they were in. This reads the Auth table and contains many different Entity Types. As I have identified them, I have also included the correct links and names for what they have access to.

NOTE: the links I used for pages may be specific to each Rock instance as pageId may vary. Make sure you check each url in the formatted output and verify it goes to the correct page in your instance.