Security Roles 'Where Used'

Question

Ever need to know on what pages security roles are being uses and if they're denied or approved?

**NOTE: This query does not take into consideration inherited permissions.

Answer 1

Select a.Action, A.AllowOrDeny, p.Id as PageId, p.[InternalName] as PageName, g.Name as GroupName
from auth a
    inner join [Page] p on a.EntityId = p.Id
    inner join [Group] g on a.GroupId = g.Id
where a.entitytypeid = 2

Answer 2

This SQL may also be helpful as it shows more information about where securities have been applied to a Page, Block, Location, etc.

--Declare @SecurityGroupName nvarchar(max) = null
Declare @SecurityGroupName nvarchar(max) = 'APP - Check-in Devices'
Declare @PageEntityTypeId int = (Select Id from EntityType where [Name] = 'Rock.Model.Page')
Declare @GroupEntityTypeId int = (Select Id from EntityType where [Name] = 'Rock.Model.Group')
Declare @RegistrationTemplateEntityTypeId int = (Select Id from EntityType where [Name] = 'Rock.Model.RegistrationTemplate')
Declare @RegistrationInstanceEntityTypeId int = (Select Id from EntityType where [Name] = 'Rock.Model.RegistrationInstance')
Declare @DataViewEntityTypeId int = (Select Id from EntityType where [Name] = 'Rock.Model.DataView')
Declare @ReportEntityTypeId int = (Select Id from EntityType where [Name] = 'Rock.Model.Report')
Declare @BlockEntityTypeId int = (Select Id from EntityType where [Name] = 'Rock.Model.Block')
Declare @ScheduleEntityTypeId int = (Select Id from EntityType where [Name] = 'Rock.Model.Schedule')
Declare @AttributeEntityTypeId int = (Select Id from EntityType where [Name] = 'Rock.Model.Attribute')
Declare @BadgeEntityTypeId int = (Select Id from EntityType where [Name] = 'Rock.Model.Badge')
select DISTINCT
    sg.Name as SecurityGroup
    , et.FriendlyName as EntityType
    , a.Action as PermisionGranted
    , a.EntityId
    , Page.InternalName as PageName
    , g.Name as GroupName
    , a.EntityTypeId as EntityType
    , et.FriendlyName as EntityName
    , a.EntityId as EntityId
    , rt.Name as RegistrationTemplateName
    , ri.Name as RegistrationInstanceName
    , dv.Name as DataviewName
    , r.Name as ReportName
    , s.Name as Schedule
    , att.Name as AttributeName
    , att.AttributeEntityType, att.EntityTypeQualifierColumn, att.EntityTypeQualifierValue
    , badge.Name as BadgeName
    , b.Name as BlockName
    , b.PageId as PageBlockIsOn
    from Auth a
    inner join EntityType et on a.EntityTypeId = et.Id
    inner join [Group] sg on a.GroupId = sg.Id
    left join Page on a.EntityId = page.Id and et.Id = @PageEntityTypeId
    left join [Group] g on a.EntityId = g.Id and et.Id = @GroupEntityTypeId
    left join RegistrationTemplate rt on a.EntityId = rt.Id and et.Id = @RegistrationTemplateEntityTypeId
    left join RegistrationInstance ri on a.EntityId = ri.Id and et.Id = @RegistrationInstanceEntityTypeId
    left join DataView dv on a.EntityId = dv.Id and et.Id = @DataViewEntityTypeId
    left join Report r on a.EntityId = r.Id and et.Id = @ReportEntityTypeId
    left join Block b on a.EntityId = b.Id and et.Id = @BlockEntityTypeId
    left join schedule s on a.EntityId = s.Id and et.Id = @ScheduleEntityTypeId
    outer apply
    (select att.Name, attet.FriendlyName as AttributeEntityType, att.EntityTypeQualifierColumn, att.EntityTypeQualifierValue
    from attribute att
        inner join EntityType attET on att.EntityTypeId = attET.Id
    where a.EntityId = att.Id and et.Id = @AttributeEntityTypeId
    ) att
    left join Badge badge on a.EntityId = badge.Id and et.Id = @BadgeEntityTypeId
    
where a.AllowOrDeny='A'
    and (@SecurityGroupName is null or @SecurityGroupName = sg.Name)
        
order by et.FriendlyName;