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

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

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

  9. select distinct c.[Name] as 'CustomerCode'
  10. , c.[Label] as 'CustomerName'
  11. , u.[Name] as 'Username'
  12. , u.[Label] as 'User DisplayName'
  13. , ( select count(*) 
  14. from ServiceUsers su 
  15. inner join Services s on s.ServiceID = su.ServiceID 
  16. where  su.UserID = u.UserID 
  17. and s.[Name] = 'AtriaMFA'
  18. ) as 'MFAEnabled'
  19. from Roles r
  20. inner join UserRoles ur on ur.RoleID = r.RoleID
  21. inner join Users u on u.UserID = ur.UserID
  22. inner join Customers c on c.CustomerID = u.CustomerID
  23. where r.[Name] not in (select [Name] from @RoleList)
  24. 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

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

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

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

    • 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 ...
    • 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 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 ...
    • 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 ...
    • Creating an Autotask API User for Atria

      Introduction In order to configure Atria to update Contracts in Autotask, you need to generate an API user and obtain the API username and password. This article takes you through the steps of setting up a Security Level and a new user. 1. Set up a ...