Sedona-X / SedonaWeb / API User Query

09/15/2026 10:47 am EDT
You can use this query to get a list of all the users in a given SedonaCloud database in one go for comparison. This will be helpful for troubleshooting logins, lockouts, tech permissions etc.
;WITH UserRoles AS
(
    SELECT
        ur.UserId,

        MAX(CASE WHEN r.Name = 'HostAdmin' THEN 1 ELSE 0 END) AS HostAdmin,
        MAX(CASE WHEN r.Name = 'CompanyAdmin' THEN 1 ELSE 0 END) AS CompanyAdmin,
        MAX(CASE WHEN r.Name = 'Company' THEN 1 ELSE 0 END) AS CompanyUser,
        MAX(CASE WHEN r.Name = 'Customer' THEN 1 ELSE 0 END) AS Customer,
        MAX(CASE WHEN r.Name = 'Perennial' THEN 1 ELSE 0 END) AS Perennial,
        MAX(CASE WHEN r.Name = 'SedonaOneMobileApp' THEN 1 ELSE 0 END) AS MobileApp,

        STRING_AGG(
            CASE
                WHEN r.Name IN
                (
                    'HostAdmin',
                    'CompanyAdmin',
                    'Company',
                    'Customer',
                    'Perennial',
                    'SedonaOneMobileApp'
                )
                THEN r.Name
            END,
            ', '
        ) AS ApplicationRole

    FROM AspNetUserRoles ur
    INNER JOIN AspNetRoles r
        ON ur.RoleId = r.Id
    GROUP BY ur.UserId
)

SELECT

    u.UserName AS [Username],
    u.FirstName AS [First Name],
    u.LastName AS [Last Name],
    u.Email AS [Email],

    ISNULL(hc.CompanyName,'<HOST USER>') AS [Company],

    hc.UrlPrefix AS [URL Prefix],

    CASE
        WHEN ISNULL(ur.HostAdmin,0)=1 THEN 'Host Admin'
        WHEN ISNULL(ur.CompanyAdmin,0)=1 THEN 'Company Admin'
        WHEN ISNULL(ur.MobileApp,0)=1 THEN 'Tech Login'
        WHEN ISNULL(ur.CompanyUser,0)=1 THEN 'Company User'
        WHEN ISNULL(ur.Perennial,0)=1 THEN 'Perennial'
        WHEN ISNULL(ur.Customer,0)=1 THEN 'Customer'
        ELSE 'Unknown'
    END AS [User Type],

    ISNULL(ur.ApplicationRole,'') AS [Application Role],
    g.GroupName AS [Tech Role Group],
    g.GroupId AS [Tech Role Group ID],
    u.Id AS [User ID],

    CASE
        WHEN u.BaseInactive = 1
            THEN 'YES'
        ELSE 'NO'
    END AS [Inactive],

    CASE
        WHEN u.LockoutEnabled = 1
         AND u.LockoutEnd > SYSUTCDATETIME()
            THEN 'YES'
        ELSE 'NO'
    END AS [Locked Out],

    u.AccessFailedCount AS [Failed Login Attempts],
    u.LockoutEnd AS [Lockout End]

FROM AspNetUsers u

LEFT JOIN HostCompany hc
    ON hc.Id = u.BaseCompanyId

LEFT JOIN UserRoles ur
    ON ur.UserId = u.Id

LEFT JOIN ASPNetGroup g
    ON g.GroupId = u.GroupId

WHERE
(
       ISNULL(ur.HostAdmin,0) = 1
    OR ISNULL(ur.CompanyAdmin,0) = 1
    OR ISNULL(ur.CompanyUser,0) = 1
    OR ISNULL(ur.MobileApp,0) = 1
    OR ISNULL(ur.Perennial,0) = 1

    -- Uncomment to include Customer Portal users
    -- OR ISNULL(ur.Customer,0) = 1
)

ORDER BY

    CASE
        WHEN hc.CompanyName IS NULL THEN 0
        ELSE 1
    END,

    [Company],
    [Username];
Was this article helpful?