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]

    • Related Articles

    • How to Manage security roles within Atria

      Manage security roles A security role is a set of permissions that defines customer, administrator, and user access to specific tasks in the Atria. For example, the first or default user created for a customer is a customer administrator. The customer ...

    • Atria API User Guide (CortexAPI)

      Overview The application programming interface (API) is a powerful interface that allows you to interact directly with Atria without using the ATRIA Web User Interface (UI).  The API grants a user, with some development knowledge, the ability to ...

    • How to Move and Copy users through the Web Management Portal

      Objective This article will guide you how to move a user from one customer to another; and copy a user to create a new user. Applies To: CloudService Portal Manager V11.x CloudService Portal Manager V11.5.x Overview User move can only be performed by ...

    • Monthly Usage Reporting to Automate101 - Powershell Script

      Overview We have heard from several customers who have been looking for an easier way to get their Atria license reporting out of Atria. Although there is a distributor report within the system, it seems that In many deployments this is not ...

    • How-To - Create Custom SQL Reports and email them to an address

      Overview Atria's Database has a lot of Customer, User and Service Data stored, but it's not feasible  to provide all staff SQL Server access to perform SQL Queries to get relevant data out of Atria, or, if a Customised report is required for a ...