Profiles at a glance!

So, you have probably had to dig around in the profile editing program in PE to find who has access to what. If you have more than a couple profiles this can be time consuming.
I put together a bit of sql which helps greatly when testing new versions (and troubleshooting why someone does not have access to something when you do...)

This will sort all profile data by profile-setting-group, profile-setting, and then by profile:



declare @m table (grp varchar(99), node varchar(99), morder int, trail varchar(256), lastparent varchar(99) )

set nocount on
insert into @m
select menus.mnugroup,mnunode,mnuorder,menus.mnugroup , menus.mnugroup
from menus menus with(nolock) where mnunode not in (select mnugroup from menus menus with(nolock)) and mnuactive = 1 order by menus.mnugroup, mnuorder

while exists (select 1 from @m inner join menus menus with(nolock) on mnunode = lastparent and mnunodedepth > 0)
begin
update @m set lastparent = mnugroup, trail = mnugroup + ' > ' + trail
from @m inner join menus menus with(nolock) on mnunode = lastparent and mnunodedepth > 0
end


SELECT
Profiles.proDescr,
isnull(m.trail , case when prmformdescr like '%???%'then prmformname else prmformdescr end) as groupname,
case when prbcontroldescr = '' then prbcontrolname else prbControlDescr end as prbControlDescr ,
convert(int,priAvailable) as priAvailable
FROM
ProfileBase ProfileBase with(nolock) INNER JOIN
ProfileInfo ProfileInfo with(nolock) ON ProfileBase.prbFormName = ProfileInfo.priFormName AND ProfileBase.prbControlName = ProfileInfo.priControlName INNER JOIN
Profiles Profiles with(nolock) ON ProfileInfo.priProfile = Profiles.proCode left outer join
@m m on m.node = prbControlname left outer join
profilemenus profilemenus with(nolock) on prmformname = prbformname
WHERE (ProfileBase.prbAvailable = 1)
order by Profiles.proDescr, isnull(m.trail , case when prmformdescr like '%???%' then prmformname else prmformdescr end),
morder, ProfileBase.prbControlDescr


You will have this access mess sorted out in no time at all! maybe even print this out for PCI ocumentation!