Query Guide: Users Table

The Users Table contains information about user records. This covers all types of user records (ex: users, customers, service accounts, etc.).

Query Example

You may use the following examples as a starter template.

Select
    Top 10
    /* User (GUID) UID */
    [GUID],
    /* User Integer ID */
    User_ID,
    User_TypeID,
    lut.UT_Name As UserTypeName,
    User_IsActive,
    User_Name,
    User_AuthProviderID,
    User_AuthProviderUsername,
    User_F_Name,
    User_L_Name,
    User_MiddleName,
    User_Nickname,
    User_Salutation,
    /* Account/Department ID */
    User_DefaultAccountID,
    /* Account/Department Name */
    cac.CAC_Desc,
    User_Company,
    User_Title,
    User_PrimaryEmail,
    User_AlternateEmail,
    User_AlertEmail,
    User_TZ_ID,
    tz.TZ_Name As TimeZoneName,
    User_Zip,
    /* Created Date */
    User_Join_Date,
    /* Global Security Role ID */
    User_RoleID,
    r.Role_Name As SecurityRoleName,
    User_PriFunctionalRoleID,
    User_ResourcePoolID,
    User_IsEmployee,
    User_ReportsToUID,
    User_LockedOut,
    User_LockedOutDate,
    /* Organizational ID */
    User_ExternalID,
    User_AlternateID,
    User_DefaultPriorityID,
    User_FullName,
    User_ModifiedUID,
    User_ModifiedDateUtc,
    User_PrimaryGroupID,
    User_AboutMe,
    User_IsUserOrCustomer,
    User_IsPhantom,
    User_LocationID,
    User_LocationRoomID,
    User_PrimaryClientAppID,
    User_IsConfidential,
    User_IsExemptFromPasswordExpiration
    /* Add or change selected columns as necessary. */
From 
    dbo.Users u With (NOLOCK)
    Inner Join dbo.LUUserTypes lut With (NOLOCK) On u.User_TypeID = lut.UT_ID
    Left Outer Join dbo.TimeZones tz With (NOLOCK) On u.User_TZ_ID = tz.TZ_ID
    Left Outer Join dbo.Roles r With (NOLOCK) On u.User_RoleID = r.Role_ID
    Left Outer Join dbo.ClientAcctCodes cac With (NOLOCK) On u.User_DefaultAccountID = cac.CAC_ID
Where
    u.BE_ID = @BEID
    /* If you wish to filter to only User and Customer types, use the below line. */
    --And u.User_IsUserOrCustomer = 1