Has anyone got a report or sql code that list all the users that are assigned to a menu security code? I have code that lists the Groups by Menu Item but need that broken down as users by menu security code and can’t get my head around it
- 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
- 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
- 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)
Attached is an Excel file that I use for this - enable Power Pivot in Excel and edit the connection (server/database) then you should be good to go
Hope this helps
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
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.