Security Roles - Get List of Security Roles by User

Security Roles - Get List of Security Roles by User

Overview

This article contains a SQL scripts to generate a report to get a list of admin users by security role declared.

We additionally have a script that notes down if they have the Atria MFA service enabled.

This solves the problem for identifying what roles with Administrative Privillages users have in Atria, or if they have MFA enabled or not.

Configuration

Declare the security roles filter which contains built-in and/or custom admin roles as filter. An example has been already set in the script below.

SQL Script to retrieve Users & Roles across Atria (All roles but general user)

  1. declare @RoleList table (
    [Name] nvarchar(50)
    )

    /* Declare list of admin roles here (including custom admin roles) */
    insert into @RoleList values 
      ('Authenticated Users')
    , ('Everyone')

    select distinct c.[Name] as 'CustomerCode'
    , c.[Label] as 'CustomerName'
    , u.[Name] as 'Username'
    , u.[Label] as 'User DisplayName'
    , r.[Name] as 'Role Name'
    from Roles r
    inner join UserRoles ur on ur.RoleID = r.RoleID
    inner join Users u on u.UserID = ur.UserID
    inner join Customers c on c.CustomerID = u.CustomerID
    where r.[Name] not in (select [Name] from @RoleList)
    order by c.[Name]

SQL Script to retrieve Roles & If MFA (Service) is enabled in Atria

  1. use olm

  3. declare @RoleList table (
  4. [Name] nvarchar(50)
  5. )

  7. /* Declare list of admin roles here (including custom admin roles) */
  8. insert into @RoleList values 
  9.    ('Authenticated Users')
  10.           ,('Everyone')

  12. select distinct c.[Name] as 'CustomerCode'
  13. , c.[Label] as 'CustomerName'
  14. , u.[Name] as 'Username'
  15. , u.[Label] as 'User DisplayName'
  16. , ( select count(*) 
  17. from ServiceUsers su 
  18. inner join Services s on s.ServiceID = su.ServiceID 
  19. where  su.UserID = u.UserID 
  20. and s.[Name] = 'AtriaMFA'
  21. ) as 'MFAEnabled'
  22. from Roles r
  23. inner join UserRoles ur on ur.RoleID = r.RoleID
  24. inner join Users u on u.UserID = ur.UserID
  25. inner join Customers c on c.CustomerID = u.CustomerID
  26. where r.[Name] not in (select [Name] from @RoleList)
  27. order by c.[Name]

SQL Script to retrieve Roles & If MFA (Service) is enabled in Atria & If MFA signup has been completed

  1. use olm

  3. declare @RoleList table (
  4. [Name] nvarchar(50)
  5. )

  7. /* Declare list of admin roles here (including custom admin roles) */
  8. insert into @RoleList values 
  9.   ('Authenticated Users')
  10.           ,('Everyone')

  12. select distinct 
  13.   c.[Name]  as 'CustomerCode'
  14. , c.[Label] as 'CustomerName'
  15. , u.[Name]  as 'Username'
  16. , u.[Label] as 'DisplayName'
  17. , ( select count(*) 
  18. from ServiceUsers su 
  19. inner join Services s on s.ServiceID = su.ServiceID 
  20. where  su.UserID = u.UserID 
  21. and s.[Name] = 'AtriaMFA'
  22. ) as 'MFAEnabled'
  23. , isnull(
  24. ( select pv.[Value] 
  25. from ServiceUsers su
  26. inner join Services s        on s.ServiceID  = su.ServiceID
  27. inner join PropertyValues pv on pv.ObjectID  = su.ObjectID
  28. inner join Properties p      on p.PropertyID = pv.PropertyID
  29. where  su.UserID = u.UserID
  30. and s.[Name] = 'AtriaMFA'
  31. and p.[Name] = 'hasCompletedSignUp'
  32. ), 'False'
  33. ) as 'CompletedSignUp'
  34. from Roles r
  35. inner join UserRoles ur on ur.RoleID    = r.RoleID
  36. inner join Users u      on u.UserID     = ur.UserID
  37. inner join Customers c  on c.CustomerID = u.CustomerID
  38. where r.[Name] not in (select [Name] from @RoleList)
  39. order by c.[Name]