User Access All WG Level Dynamic

Generates a dynamic report of user access across all workgroup levels, showing which users have access to which workgroups and their corresponding permissions.
Parameters

None (or list any script parameters if present).
Notes

Useful for auditing user access and permissions across multiple workgroups.
Ensure appropriate permissions to access user and workgroup data.
T-SQL

useraccess_all_wg_lvl_dynamic.sql
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Declare @User_Names Table (Name Varchar(30),
    ID int,
    UserGroup varchar(30),
    Profile varchar(50))

Insert into @User_Names
Select
    u.FRIENDLYNAME,
    u.UNIQUEID,
    UG.NAME,
    UP.MENUPROF

From USERS U
    inner Join USERGROUP UG
    on U.USERGROUP = UG.num
    inner Join USERPROP UP
    on u.UNIQUEID = up.USERID
where u.FRIENDLYNAME not in ('ESSUSER','Default Setup Account','Default System Account','Default Service Account','ATM Service Account','ATM Service 2',
	'ATM User','Auto Service Account','Employee Self Service Account','AoD Administrative Account','AoD Technicians Account','AoD Accounting Account','Technicians Account',
	'Accounting Account','Sales #1 Account','Sales #2 Account','Sales #3 Account')

If (Exists (Select *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'WORKGROUP7'))

With
    WG_Sets_Names
    as
    (
        Select
            ua.USERID as ID,
            Case when ua.WG1 =  0 then 'All' else w1.NAME end as WG1,
            Case when ua.WG2 =  0 then 'All' else w2.NAME end as WG2,
            Case when ua.WG3 =  0 then 'All' else w3.NAME end as WG3,
            Case when ua.WG4 =  0 then 'All' else w4.NAME end as WG4,
            Case when ua.WG5 =  0 then 'All' else w5.NAME end as WG5,
            Case when ua.WG6 =  0 then 'All' else w6.NAME end as WG6,
            Case when ua.WG7 =  0 then 'All' else w7.NAME end as WG7

        FROM USERACCESS ua
            left join WORKGROUP1 w1 on
  ua.WG1 = w1.WGNUM
            left join WORKGROUP2 w2 on
  ua.WG2 = w2.WGNUM
            left join WORKGROUP3 w3 on
  ua.WG3 = w3.WGNUM
            left join WORKGROUP4 w4 on
  ua.WG4 = w4.WGNUM
            left join WORKGROUP5 w5 on
  ua.WG5 = w5.WGNUM
            left join WORKGROUP6 w6 on
  ua.WG6 = w6.WGNUM
            left join WORKGROUP7 w7 on
  ua.WG7 = w7.WGNUM
    )
    Select ID, NAME, 'UG - '+UserGroup as WG1, 'Profile - '+Profile as WG2, null as WG3, null as WG4, null as WG5, null as WG6, null as WG7
    from @User_Names
UNION
    Select ws.ID, 'zzz', ws.WG1, ws.wg2, ws.WG3, ws.WG4, ws.WG5, ws.wg6, ws.WG7
    from WG_Sets_Names WS, @User_Names UN
    where ws.ID = un.ID
order by 1,2

ELSE If (Exists (Select *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'WORKGROUP6'))

With
    WG_Sets_Names
    as
    (
        Select
            ua.USERID as ID,
            Case when ua.WG1 =  0 then 'All' else w1.NAME end as WG1,
            Case when ua.WG2 =  0 then 'All' else w2.NAME end as WG2,
            Case when ua.WG3 =  0 then 'All' else w3.NAME end as WG3,
            Case when ua.WG4 =  0 then 'All' else w4.NAME end as WG4,
            Case when ua.WG5 =  0 then 'All' else w5.NAME end as WG5,
            Case when ua.WG6 =  0 then 'All' else w6.NAME end as WG6

        FROM USERACCESS ua
            left join WORKGROUP1 w1 on
  ua.WG1 = w1.WGNUM
            left join WORKGROUP2 w2 on
  ua.WG2 = w2.WGNUM
            left join WORKGROUP3 w3 on
  ua.WG3 = w3.WGNUM
            left join WORKGROUP4 w4 on
  ua.WG4 = w4.WGNUM
            left join WORKGROUP5 w5 on
  ua.WG5 = w5.WGNUM
            left join WORKGROUP6 w6 on
  ua.WG6 = w6.WGNUM
    )
    Select ID, NAME, 'UG - '+UserGroup as WG1, 'Profile - '+Profile as WG2, null as WG3, null as WG4, null as WG5, null as WG6
    from @User_Names
UNION
    Select ws.ID, 'zzz', ws.WG1, ws.wg2, ws.WG3, ws.WG4, ws.WG5, ws.wg6
    from WG_Sets_Names WS, @User_Names UN
    where ws.ID = un.ID
order by 1,2

ELSE IF (Exists (Select *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'WORKGROUP5'))

With
    WG_Sets_Names
    as
    (
        Select
            ua.USERID as ID,
            Case when ua.WG1 =  0 then 'All' else w1.NAME end as WG1,
            Case when ua.WG2 =  0 then 'All' else w2.NAME end as WG2,
            Case when ua.WG3 =  0 then 'All' else w3.NAME end as WG3,
            Case when ua.WG4 =  0 then 'All' else w4.NAME end as WG4,
            Case when ua.WG5 =  0 then 'All' else w5.NAME end as WG5

        FROM USERACCESS ua
            left join WORKGROUP1 w1 on
  ua.WG1 = w1.WGNUM
            left join WORKGROUP2 w2 on
  ua.WG2 = w2.WGNUM
            left join WORKGROUP3 w3 on
  ua.WG3 = w3.WGNUM
            left join WORKGROUP4 w4 on
  ua.WG4 = w4.WGNUM
            left join WORKGROUP5 w5 on
  ua.WG5 = w5.WGNUM
    )
    Select ID, NAME, 'UG - '+UserGroup as WG1, 'Profile - '+Profile as WG2, null as WG3, null as WG4, null as WG5
    from @User_Names
UNION
    Select ws.ID, 'zzz', ws.WG1, ws.wg2, ws.WG3, ws.WG4, ws.WG5
    from WG_Sets_Names WS, @User_Names UN
    where ws.ID = un.ID
order by 1,2

ELSE If (Exists (Select *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'WORKGROUP4'))

With
    WG_Sets_Names
    as
    (
        Select
            ua.USERID as ID,
            Case when ua.WG1 =  0 then 'All' else w1.NAME end as WG1,
            Case when ua.WG2 =  0 then 'All' else w2.NAME end as WG2,
            Case when ua.WG3 =  0 then 'All' else w3.NAME end as WG3,
            Case when ua.WG4 =  0 then 'All' else w4.NAME end as WG4

        FROM USERACCESS ua
            left join WORKGROUP1 w1 on
  ua.WG1 = w1.WGNUM
            left join WORKGROUP2 w2 on
  ua.WG2 = w2.WGNUM
            left join WORKGROUP3 w3 on
  ua.WG3 = w3.WGNUM
            left join WORKGROUP4 w4 on
  ua.WG4 = w4.WGNUM
    )
    Select ID, NAME, 'UG - '+UserGroup as WG1, 'Profile - '+Profile as WG2, null as WG3, null as WG4
    from @User_Names
UNION
    Select ws.ID, 'zzz', ws.WG1, ws.wg2, ws.WG3, ws.WG4
    from WG_Sets_Names WS, @User_Names UN
    where ws.ID = un.ID
order by 1,2

ELSE

with
    WG_Sets_Names
    as
    (
        Select
            ua.USERID as ID,
            Case when ua.WG1 =  0 then 'All' else w1.NAME end as WG1,
            Case when ua.WG2 =  0 then 'All' else w2.NAME end as WG2,
            Case when ua.WG3 =  0 then 'All' else w3.NAME end as WG3

        FROM USERACCESS ua
            left join WORKGROUP1 w1 on
  ua.WG1 = w1.WGNUM
            left join WORKGROUP2 w2 on
  ua.WG2 = w2.WGNUM
            left join WORKGROUP3 w3 on
  ua.WG3 = w3.WGNUM
    )
    Select ID, NAME, 'UG - '+UserGroup as WG1, 'Profile - '+Profile as WG2, null as WG3
    from @User_Names
UNION
    Select ws.ID, 'zzz', ws.WG1, ws.wg2, ws.WG3
    from WG_Sets_Names WS, @User_Names UN
    where ws.ID = un.ID
order by 1,2
Content Inventory

Doc File: content/docs/reports/users_information/useraccess_all_wg_lvl_dynamic.mdx
SQL Script: SQL/reports/users_information/useraccess_all_wg_lvl_dynamic.sql
User Access All WG Level Dynamic

Parameters

Notes

T-SQL

Content Inventory

On this page
