V_QUERY_SYSTEMROLEAPPUSER
Joins System Role to Application Users.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| USERNAME | nvarchar(128) | yes | User name |
| DISPLAYNAME | nvarchar(255) | yes | Display name |
| SYSTEMROLEID | uniqueidentifier | System role ID | |
| APPUSERID | uniqueidentifier | App user ID | |
| 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 |
| USERSID | nvarchar(max) | yes | NT account SID |
| ISSYSADMIN | bit | yes | Is system admin |
| ROLE_ASSIGNMENTS | int | yes | Assigned role count |
| ISSYNC | bit | Synchronized | |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| APPUSERADDEDBY_APPLICATION | nvarchar(200) | yes | User added by application |
| APPUSERADDEDBY_USERNAME | nvarchar(128) | yes | User added by user name |
| APPUSERCHANGEDBY_APPLICATION | nvarchar(200) | yes | User changed by application |
| APPUSERCHANGEDBY_USERNAME | nvarchar(128) | yes | User changed by user name |
| APPUSERDATEADDED | datetime | yes | User date added |
| APPUSERDATECHANGED | datetime | yes | User date changed |
| APPUSERTSLONG | bigint | yes | User timestamp value |
| SECURITYMODE | nvarchar(40) | yes | Site security |
| BRANCHSITENAME | nvarchar(250) | yes | Branch |
| SITEID | uniqueidentifier | yes | Site ID |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:20:43 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SYSTEMROLEAPPUSER AS
select
SYSTEMROLEAPPUSER.ID,
APPUSER.USERNAME,
APPUSER.DISPLAYNAME,
SYSTEMROLEAPPUSER.SYSTEMROLEID,
SYSTEMROLEAPPUSER.APPUSERID,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
dbo.UFN_VARBINTOHEXSUBSTRING(1,APPUSER.USERSID,1,0) as USERSID,
APPUSER.ISSYSADMIN,
(select COUNT(*) from dbo.SYSTEMROLEAPPUSER as SRAU where SRAU.APPUSERID = APPUSER.ID) AS 'ROLE_ASSIGNMENTS',
SYSTEMROLEAPPUSER.FROMROLESYNC AS ISSYNC,
SYSTEMROLEAPPUSER.DATEADDED,
SYSTEMROLEAPPUSER.DATECHANGED,
SYSTEMROLEAPPUSER.TSLONG,
[APPUSERADDEDBY].APPLICATIONNAME as [APPUSERADDEDBY_APPLICATION],
[APPUSERADDEDBY].USERNAME as [APPUSERADDEDBY_USERNAME],
[APPUSERCHANGEDBY].APPLICATIONNAME as [APPUSERCHANGEDBY_APPLICATION],
[APPUSERCHANGEDBY].USERNAME as [APPUSERCHANGEDBY_USERNAME],
APPUSER.DATEADDED AS APPUSERDATEADDED,
APPUSER.DATECHANGED AS APPUSERDATECHANGED,
APPUSER.TSLONG AS APPUSERTSLONG,
SYSTEMROLEAPPUSER.SECURITYMODE,
SITE.NAME as BRANCHSITENAME,
SITE.ID as SITEID
/*#EXTENSION*/
from
dbo.SYSTEMROLEAPPUSER
left join dbo.APPUSER on SYSTEMROLEAPPUSER.APPUSERID = APPUSER.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SYSTEMROLEAPPUSER.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SYSTEMROLEAPPUSER.CHANGEDBYID
left join dbo.CHANGEAGENT as [APPUSERADDEDBY] on [APPUSERADDEDBY].ID = APPUSER.ADDEDBYID
left join dbo.CHANGEAGENT as [APPUSERCHANGEDBY] on [APPUSERCHANGEDBY].ID = APPUSER.CHANGEDBYID
left join dbo.SITE on SITE.ID = SYSTEMROLEAPPUSER.BRANCHSITEID