V_QUERY_SYSTEMROLEADGROUP
List of Active Directory/NT groups setup to synchronize to a System role.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| SYSTEMROLEID | uniqueidentifier | System role ID | |
| GROUPSID | nvarchar(max) | yes | Group SID |
| SOURCETYPE | varchar(10) | Source | |
| DISPLAYNAME | nvarchar(255) | Display name | |
| LDAPQUERY | nvarchar(4000) | LDAP query | |
| SEARCHROOT | nvarchar(1024) | Search root | |
| INCLUDESUBCONTAINERS | bit | Include subcontainers | |
| GROUPNAME | nvarchar(128) | yes | Group name |
| 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 |
| SECURITYMODE | nvarchar(40) | yes | Site security |
| BRANCHSITENAME | nvarchar(250) | yes | Branch |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:20:40 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SYSTEMROLEADGROUP AS
select
SYSTEMROLEADGROUP.ID,
SYSTEMROLEADGROUP.SYSTEMROLEID,
dbo.UFN_VARBINTOHEXSUBSTRING(1,SYSTEMROLEADGROUP.GROUPID,1,0) as GROUPSID,
case when
SYSTEMROLEADGROUP.GROUPID is null
then
'LDAP query'
else
'Group'
end as SOURCETYPE,
DISPLAYNAME,
LDAPQUERY,
SEARCHROOT,
INCLUDESUBCONTAINERS,
GROUPNAME,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SYSTEMROLEADGROUP.DATEADDED,
SYSTEMROLEADGROUP.DATECHANGED,
SYSTEMROLEADGROUP.TSLONG,
SYSTEMROLEADGROUP.SECURITYMODE,
SITE.NAME as BRANCHSITENAME
/*#EXTENSION*/
from
dbo.SYSTEMROLEADGROUP
left outer join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SYSTEMROLEADGROUP.ADDEDBYID
left outer join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SYSTEMROLEADGROUP.CHANGEDBYID
left outer join dbo.SITE on SITE.ID = SYSTEMROLEADGROUP.BRANCHSITEID