V_QUERY_SYSTEMROLEADGROUPSITE
List of all sites assigned to an active directory group.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| SYSTEMROLEADGROUPID | uniqueidentifier | SYSTEMROLEADGROUPID | |
| SITENAME | nvarchar(250) | Name | |
| DESCRIPTION | nvarchar(max) | Description | |
| 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 |
| SITEID | uniqueidentifier | Site record | |
| SITESHORTNAME | nvarchar(100) | Short name | |
| SITESITEID | nvarchar(100) | Site ID | |
| SITEACRONYM | nvarchar(100) | Acronym | |
| SITETYPECODE | nvarchar(100) | yes | Site type |
| SITEPATH | nvarchar(1000) | yes | Site path |
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_SYSTEMROLEADGROUPSITE AS
select
SYSTEMROLEADGROUPSITE.ID,
SYSTEMROLEADGROUPSITE.SYSTEMROLEADGROUPID,
SITE.NAME as SITENAME,
SITE.DESCRIPTION,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SYSTEMROLEADGROUPSITE.DATEADDED,
SYSTEMROLEADGROUPSITE.DATECHANGED,
SYSTEMROLEADGROUPSITE.TSLONG,
SITE.ID as SITEID,
SITE.SHORTNAME as SITESHORTNAME,
SITE.SITEID as SITESITEID,
SITE.ACRONYM as SITEACRONYM,
SITETYPECODE.DESCRIPTION as SITETYPECODE,
SITE.SITEPATH
/*#EXTENSION*/
from
dbo.SYSTEMROLEADGROUPSITE
inner join
(
select
SITE.ID,
SITE.DESCRIPTION,
SITE.SHORTNAME,
SITE.SITEID,
SITE.ACRONYM,
SITE.NAME,
SITE.SITETYPECODEID,
UFN_SITE_GETPATH_BULK.SITEPATH
from dbo.SITE
cross apply dbo.UFN_SITE_GETPATH_BULK(SITE.ID)
where UFN_SITE_GETPATH_BULK.ID = SITE.ID
) as SITE on SITE.ID = SYSTEMROLEADGROUPSITE.SITEID
left join dbo.SITETYPECODE on SITE.SITETYPECODEID = SITETYPECODE.ID
left outer join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SYSTEMROLEADGROUPSITE.ADDEDBYID
left outer join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SYSTEMROLEADGROUPSITE.CHANGEDBYID