Users by Menu SECCODE

1.  Create a view called MenuList as

ALTER View dbo.MenuList as 
SELECT

M.MENUID, M.MENUDESC, M.PARENTMENUID, X.MENUDESC AS 'PARENT_MENU_DESC',

M.PROGRAM, M.SECCODE, S.SECURITYMGR, S.ENTRYLIST ACCESSLIST,  S.DESCRIPTION

FROM EpicorERP.Ice.MENU M

INNER JOIN EpicorERP.IcE.SECURITY S ON S.SECCODE = M.SECCODE

INNER JOIN EpicorERP.Ice.MENU X ON M.PARENTMENUID = X.MENUID

WHERE M.OPTIONTYPE = 'I'
GO

2. create a view for users and group

create  View [dbo].[UsersAndGroups] as 
SELECT userfile.DcdUserID UserName, userfile.Name EmployeeName, secgroup.Secgroupcode, secgroup.Secgroupdesc
FROM EpicorErp.Erp.UserFile UserFile
CROSS JOIN
EpicorERP.Ice.SecGroup SecGroup
WHERE ( (userfile.grouplist LIKE '%' + secgroup.secgroupcode + '~%') OR
(userfile.grouplist LIKE '%' + secgroup.secgroupcode)) and UserFile.UserDisabled = 0

3. Combine the two

Select   ml.MENUDESC, ml.PARENT_MENU_DESC as Parent, ml.DESCRIPTION MenuItem, ug.EmployeeName, ug.UserName
From dbo.MenuList ml
Cross Join
dbo.UsersAndGroups ug 
where( ml.ACCESSLIST like '%' + ug.Secgroupcode + '%' OR  ml.ACCESSLIST like '%' + ug.Secgroupcode)

If you just want the SQL:

WITH SplitStringUserCompany AS
(
    SELECT
        DCDUserID,LEFT(CompList,CHARINDEX('~',CompList)-1) AS UserCompany
            ,RIGHT(CompList,LEN(CompList)-CHARINDEX('~',CompList)) AS Remainder
        FROM erp.UserFile
        WHERE CompList IS NOT NULL AND CHARINDEX('~',CompList)>0
    UNION ALL
    SELECT
        DCDUserID,LEFT(Remainder,CHARINDEX('~',Remainder)-1)
            ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX('~',Remainder))
        FROM SplitStringUserCompany
        WHERE Remainder IS NOT NULL AND CHARINDEX('~',Remainder)>0
    UNION ALL
    SELECT
        DCDUserID,Remainder,null
        FROM SplitStringUserCompany
        WHERE Remainder IS NOT NULL AND CHARINDEX('~',Remainder)=0
),
CTEUserCompany AS
(

SELECT 
u.DCDUserID,
UserCompany = 
CASE 
WHEN (LEN([CompList]) - LEN(REPLACE([CompList],'~','')))/COALESCE(NULLIF(LEN('~'), 0), 1) = 0 THEN CompList ELSE ss.UserCompany
END

FROM
erp.UserFile u

LEFT JOIN
SplitStringUserCompany ss on
u.DcdUserID = ss.DcdUserID)
,
SplitStringUserGroup AS
(
    SELECT
        DCDUserID,LEFT(GroupList,CHARINDEX('~',GroupList)-1) AS UserGroup
            ,RIGHT(GroupList,LEN(GroupList)-CHARINDEX('~',GroupList)) AS Remainder
        FROM erp.UserFile
        WHERE GroupList IS NOT NULL AND CHARINDEX('~',GroupList)>0
    UNION ALL
    SELECT
        DCDUserID,LEFT(Remainder,CHARINDEX('~',Remainder)-1)
            ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX('~',Remainder))
        FROM SplitStringUserGroup
        WHERE Remainder IS NOT NULL AND CHARINDEX('~',Remainder)>0
    UNION ALL
    SELECT
        DCDUserID,Remainder,null
        FROM SplitStringUserGroup
        WHERE Remainder IS NOT NULL AND CHARINDEX('~',Remainder)=0
),
CTEUsersGroups AS
(

SELECT 
u.DCDUserID,
UserGroup = 
CASE 
WHEN (LEN([GroupList]) - LEN(REPLACE([GroupList],'~','')))/COALESCE(NULLIF(LEN('~'), 0), 1) = 0 THEN GroupList ELSE ss.UserGroup
END

FROM
erp.UserFile u

LEFT JOIN
SplitStringUserGroup ss on
u.DcdUserID = ss.DcdUserID
),

CTEMenuSecurity 
AS
(
SELECT 
m1.MenuID, 
m1.MenuDesc, 
m1.SecCode, 
s1.EntryList, 
m1.ParentMenuID, 
FullMenuPath = 
CAST(m1.MenuDesc AS VARCHAR(255)) , 1 AS MenuLevel
FROM Ice.Menu AS m1
JOIN 
Ice.Security s1 ON 
m1.SecCode=s1.SecCode
WHERE m1.ParentMenuID=''

UNION ALL
SELECT 
m2.MenuID, 
m2.MenuDesc, 
m2.SecCode, 
s2.EntryList, 
m2.ParentMenuID, 
FullMenu = 
CAST(FullMenuPath+ ' - ' + CAST(m2.MenuDesc AS VARCHAR(255)) AS VARCHAR(255)) , (MenuLevel + 1) AS MenuLevel
FROM Ice.Menu m2

JOIN CTEMenuSecurity ON 
CTEMenuSecurity.MenuID=m2.ParentMenuID

JOIN Ice.Security s2 ON 
m2.SecCode=s2.SecCode
)
,
SplitSting AS
(
    SELECT
        MenuID,LEFT(EntryList,CHARINDEX(',',EntryList)-1) AS UserGroup
            ,RIGHT(EntryList,LEN(EntryList)-CHARINDEX(',',EntryList)) AS Remainder
        FROM CTEMenuSecurity
        WHERE EntryList IS NOT NULL AND CHARINDEX(',',EntryList)>0
    UNION ALL
    SELECT
        MenuID,LEFT(Remainder,CHARINDEX(',',Remainder)-1)
            ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
        FROM SplitSting
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
    UNION ALL
    SELECT
        MenuID,Remainder,null
        FROM SplitSting
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0
),

CTEUsersGroupsMenus AS
(

SELECT 
FullMenuPath,
UserGroup,
MenuLevel

FROM CTEMenuSecurity cte

LEFT JOIN
SplitSting ss on
cte.MenuID = ss.MenuID
)

select 
uc.UserCompany,
[User ID ] = 
ug.DCDUserID,
[User Name] =
uf.Name,
[SecurityGroup] = 
ISNULL(sg.SecGroupDesc,um2.UserGroup),
[Menu Path] = 
ISNULL(um.FullMenuPath, um2.FullMenuPath),
uf.UserDisabled

from CTEUsersGroups ug

LEFT JOIN
erp.UserFile uf on
ug.DCDUSerID = uf.DCDUserID

LEFT JOIN
ice.SecGroup sg on
ug.UserGroup = sg.SecGroupCode

LEFT JOIN
CTEUsersGroupsMenus um on
ug.UserGroup = um.UserGroup

LEFT JOIN
CTEUsersGroupsMenus um2 on
ug.DCDUSerID = um2.UserGroup

LEFT JOIN
CTEUserCompany uc on
ug.DcdUserID = uc.DcdUserID