Hello Everyone,
I need to create a query that can show me users that have permissions to a function through menu maintance. I currently have the entire Menu Access by Security Group. But I been asked to proove no individual user has access to a menu or spefic function. I would appreciate any ideas how to prove this.
Thanks in Advance.
Miguel
I’m not sure I understand, can you give some examples?
In the meantime… deciphering menu security in Epicor from reports or queries can be a challenge (at least for me). Stock reports can be unwieldy and in E9, BAQ capabilities are somewhat limited (if you are on E10 of have access to the SQL db, indented queries are possible…a little better)
So instead what I used to verify menu security
a.) Copy LIVE to TEST
b.) IN TEST, reset user passwords (or create dummy users with corresponding security)
c.) Log on to TEST as user(s) in question
d.) Manually traverse menus.
Tedious, yes but… at least this way you can be 100% sure who has access.
(It REALLY helps if your security groups are in order and maintained on user accounts).
And FWIW… below E10 BAQ query phrase for an indented menu I have used to look at security
with [MenuCTE] as
(select
[BaseMenu].[Menu_MenuID] as [Menu_MenuID],
[BaseMenu].[Menu_MenuDesc] as [Menu_MenuDesc],
[BaseMenu].[Menu_ParentMenuID] as [Menu_ParentMenuID],
[BaseMenu].[Calculated_Level] as [Calculated_Level],
[BaseMenu].[Calculated_FullPath] as [Calculated_FullPath],
[BaseMenu].[Menu_SecCode] as [Menu_SecCode]
from (select
[Menu].[MenuID] as [Menu_MenuID],
[Menu].[MenuDesc] as [Menu_MenuDesc],
[Menu].[ParentMenuID] as [Menu_ParentMenuID],
(1) as [Calculated_Level],
(cast('' as nvarchar(255))) as [Calculated_FullPath],
[Menu].[SecCode] as [Menu_SecCode]
from Ice.Menu as Menu
where (Menu.ParentMenuID = '')) as BaseMenu
union all
select
[UnionMenu].[MenuID] as [UnionMenu_MenuID],
[UnionMenu].[MenuDesc] as [UnionMenu_MenuDesc],
[UnionMenu].[ParentMenuID] as [UnionMenu_ParentMenuID],
(MenuCTE1.Calculated_Level + 1) as [Calculated_Level],
(cast(MenuCTE1.Calculated_FullPath + MenuCTE1.Menu_MenuDesc + '>' as nvarchar(255))) as [Calculated_FullPath],
[UnionMenu].[SecCode] as [UnionMenu_SecCode]
from Ice.Menu as UnionMenu
inner join MenuCTE as MenuCTE1 on
UnionMenu.ParentMenuID = MenuCTE1.Menu_MenuID
where (UnionMenu.MenuID is not null))
select
[MenuCTE].[Calculated_Level] as [Calculated_Level],
[MenuCTE].[Calculated_FullPath] as [Calculated_FullPath],
[MenuCTE].[Menu_ParentMenuID] as [Menu_ParentMenuID],
[MenuDetails].[Sequence] as [MenuDetails_Sequence],
[MenuCTE].[Menu_MenuID] as [Menu_MenuID],
[MenuCTE].[Menu_MenuDesc] as [Menu_MenuDesc],
[Security].[SecCode] as [Security_SecCode],
[Security].[EntryList] as [Security_EntryList],
[Security].[NoEntryList] as [Security_NoEntryList],
[Security].[SecurityMgr] as [Security_SecurityMgr],
[MenuDetails].[OptionType] as [MenuDetails_OptionType],
[MenuDetails].[OptionSubType] as [MenuDetails_OptionSubType],
[MenuDetails].[MenuType] as [MenuDetails_MenuType],
[MenuDetails].[Arguments] as [MenuDetails_Arguments],
[MenuDetails].[Module] as [MenuDetails_Module],
[MenuDetails].[DashboardID] as [MenuDetails_DashboardID],
[MenuDetails].[Program] as [MenuDetails_Program],
[MenuDetails].[SecCode] as [MenuDetails_SecCode]
from MenuCTE as MenuCTE
left outer join Ice.Security as Security on
MenuCTE.Menu_SecCode = Security.SecCode
inner join Ice.Menu as MenuDetails on
MenuCTE.Menu_MenuID = MenuDetails.MenuID
order by MenuCTE.Calculated_Level, MenuCTE.Menu_MenuID
and screenshot of resulting data in a dashboard grid
