V_QUERY_APPUSER
List of all Application users.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| USERNAME | nvarchar(128) | yes | Name |
| DISPLAYNAME | nvarchar(255) | Display name | |
| USERSID | nvarchar(max) | yes | NT account SID |
| ISSYSADMIN | bit | Is system admin | |
| ROLE_ASSIGNMENTS | int | yes | Assigned role count |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| SITENAME | nvarchar(250) | Site | |
| CURRENCYSETID | uniqueidentifier | yes | Currency set |
| SITEID | uniqueidentifier | yes | Site ID |
| EMAILADDRESS | nvarchar(100) | yes | |
| ISACTIVE | bit | ||
| INACTIVEREASON | nvarchar(100) | yes | |
| INACTIVEREASONDETAILS | nvarchar(300) | yes | |
| STATUS | nvarchar(17) | ||
| LASTLOGINDATE | datetime | yes | |
| HASRUNASRIGHTS | bit |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 5/3/2024 2:17:11 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_APPUSER AS
select
APPUSER.ID,
APPUSER.USERNAME,
APPUSER.DISPLAYNAME,
dbo.UFN_VARBINTOHEXSUBSTRING(1,APPUSER.USERSID,1,0) as USERSID,
APPUSER.ISSYSADMIN,
(select count(*) from dbo.SYSTEMROLEAPPUSER where SYSTEMROLEAPPUSER.APPUSERID = APPUSER.ID) AS 'ROLE_ASSIGNMENTS',
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
APPUSER.DATEADDED,
APPUSER.DATECHANGED,
APPUSER.TSLONG,
isnull((select NAME from dbo.SITE where SITE.ID = APPUSER.SITEID),'') AS SITENAME,
APPUSERCURRENCYSET.CURRENCYSETID,
APPUSER.SITEID,
APPUSER.EMAILADDRESS,
isnull(APPUSER.ISACTIVE, 1) as ISACTIVE,
APPUSERINACTIVECODE.DESCRIPTION as INACTIVEREASON,
APPUSERINACTIVEDETAIL.DETAILS as INACTIVEREASONDETAILS,
case
when isnull(APPUSERTHIRDPARTYAUTH.INACTIVE, 0) = 0 then isnull(APPUSERTHIRDPARTYAUTH.INVITATIONSTATUS, 'Not yet invited')
else 'Inactive'
end as STATUS,
APPUSER.LASTLOGINDATE AS LASTLOGINDATE,
isnull(APPUSER.HASRUNASRIGHTS,0) as HASRUNASRIGHTS
/*#EXTENSION*/
from
dbo.APPUSER
left join dbo.APPUSERCURRENCYSET on APPUSER.ID = APPUSERCURRENCYSET.APPUSERID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = APPUSER.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = APPUSER.CHANGEDBYID
left join dbo.APPUSERINACTIVEDETAIL as APPUSERINACTIVEDETAIL on APPUSERINACTIVEDETAIL.ID = APPUSER.ID
left join dbo.APPUSERINACTIVECODE as APPUSERINACTIVECODE on APPUSERINACTIVECODE.ID = APPUSERINACTIVEDETAIL.APPUSERINACTIVEREASONCODEID
left join dbo.APPUSERTHIRDPARTYAUTH on APPUSER.ID = APPUSERTHIRDPARTYAUTH.ID;