V_QUERY_SYSTEMROLEFEATUREPERMS
List of feature permissions that have been assigned to a role.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| SYSTEMROLEID | uniqueidentifier | System role ID | |
| FEATUREID | uniqueidentifier | Feature ID | |
| FEATURETYPE | varchar(16) | Feature type | |
| GRANTORDENY | varchar(5) | Access | |
| FEATURENAME | nvarchar(255) | Feature name | |
| FEATUREDESCRIPTION | nvarchar(max) | Feature description | |
| SECURITYUIFOLDER | nvarchar(255) | Security folder | |
| 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 |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:37:33 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SYSTEMROLEFEATUREPERMS AS
select
SRP.ID,
SRP.SYSTEMROLEID,
SRP.BUSINESSPROCESSCATALOGID AS FEATUREID,
'Business Process' as FEATURETYPE,
CASE SRP.GRANTORDENY WHEN 1 THEN 'Grant' WHEN 0 THEN 'Deny' else 'None' END AS GRANTORDENY,
C.[NAME] AS FEATURENAME,
C.DESCRIPTION AS FEATUREDESCRIPTION,
C.SECURITYUIFOLDER,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SRP.DATEADDED,
SRP.DATECHANGED,
SRP.TSLONG
from dbo.SYSTEMROLEPERM_BUSINESSPROCESS AS SRP
join dbo.V_INSTALLED_BUSINESSPROCESSCATALOG as C on SRP.BUSINESSPROCESSCATALOGID=C.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SRP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SRP.CHANGEDBYID
UNION ALL
select
SRP.ID,
SRP.SYSTEMROLEID,
SRP.DASHBOARDCATALOGID AS FEATUREID,
'Dashboard' as FEATURETYPE,
CASE SRP.GRANTORDENY WHEN 1 THEN 'Grant' WHEN 0 THEN 'Deny' else 'None' END AS GRANTORDENY,
C.[NAME] AS FEATURENAME,
C.DESCRIPTION AS FEATUREDESCRIPTION,
C.SECURITYUIFOLDER,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SRP.DATEADDED,
SRP.DATECHANGED,
SRP.TSLONG
from dbo.SYSTEMROLEPERM_DASHBOARD AS SRP
join dbo.V_INSTALLED_DASHBOARDCATALOG as C on SRP.DASHBOARDCATALOGID=C.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SRP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SRP.CHANGEDBYID
UNION ALL
select
SRP.ID,
SRP.SYSTEMROLEID,
SRP.DATAFORMINSTANCECATALOGID AS FEATUREID,
'Data Form' as FEATURETYPE,
CASE SRP.GRANTORDENY WHEN 1 THEN 'Grant' WHEN 0 THEN 'Deny' else 'None' END AS GRANTORDENY,
DFIC.[FORMNAME] AS FEATURENAME,
DFIC.DESCRIPTION AS FEATUREDESCRIPTION,
C.SECURITYUIFOLDER,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SRP.DATEADDED,
SRP.DATECHANGED,
SRP.TSLONG
from dbo.SYSTEMROLEPERM_DATAFORMINSTANCE AS SRP
join dbo.DATAFORMINSTANCECATALOG as DFIC on SRP.DATAFORMINSTANCECATALOGID=DFIC.ID
join dbo.V_INSTALLED_DATAFORMTEMPLATECATALOG as C on DFIC.DATAFORMTEMPLATECATALOGID=C.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SRP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SRP.CHANGEDBYID
UNION ALL
select
SRP.ID,
SRP.SYSTEMROLEID,
SRP.DATALISTCATALOGID AS FEATUREID,
'Data List' as FEATURETYPE,
CASE SRP.GRANTORDENY WHEN 1 THEN 'Grant' WHEN 0 THEN 'Deny' else 'None' END AS GRANTORDENY,
C.[NAME] AS FEATURENAME,
C.DESCRIPTION AS FEATUREDESCRIPTION,
C.SECURITYUIFOLDER,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SRP.DATEADDED,
SRP.DATECHANGED,
SRP.TSLONG
from dbo.SYSTEMROLEPERM_DATALIST AS SRP
join dbo.V_INSTALLED_DATALISTCATALOG as C on SRP.DATALISTCATALOGID=C.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SRP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SRP.CHANGEDBYID
UNION ALL
select
SRP.ID,
SRP.SYSTEMROLEID,
SRP.RECORDOPERATIONCATALOGID AS FEATUREID,
'Record Operation' as FEATURETYPE,
CASE SRP.GRANTORDENY WHEN 1 THEN 'Grant' WHEN 0 THEN 'Deny' else 'None' END AS GRANTORDENY,
C.[DISPLAYNAME] AS FEATURENAME,
C.DESCRIPTION AS FEATUREDESCRIPTION,
C.SECURITYUIFOLDER,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SRP.DATEADDED,
SRP.DATECHANGED,
SRP.TSLONG
from dbo.SYSTEMROLEPERM_RECORDOPERATION AS SRP
join dbo.V_INSTALLED_RECORDOPERATIONCATALOG as C on SRP.RECORDOPERATIONCATALOGID=C.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SRP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SRP.CHANGEDBYID
UNION ALL
select
SRP.ID,
SRP.SYSTEMROLEID,
SRP.SMARTQUERYCATALOGID AS FEATUREID,
'Smart Query' as FEATURETYPE,
CASE SRP.GRANTORDENY WHEN 1 THEN 'Grant' WHEN 0 THEN 'Deny' else 'None' END AS GRANTORDENY,
C.[NAME] AS FEATURENAME,
C.DESCRIPTION AS FEATUREDESCRIPTION,
C.SECURITYUIFOLDER,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SRP.DATEADDED,
SRP.DATECHANGED,
SRP.TSLONG
from dbo.SYSTEMROLEPERM_SMARTQUERY AS SRP
join dbo.V_INSTALLED_SMARTQUERYCATALOG as C on SRP.SMARTQUERYCATALOGID=C.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SRP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SRP.CHANGEDBYID
UNION ALL
select
SRP.ID,
SRP.SYSTEMROLEID,
SRP.MAPENTITYCATALOGID AS FEATUREID,
'Map Entity' as FEATURETYPE,
CASE SRP.GRANTORDENY WHEN 1 THEN 'Grant' WHEN 0 THEN 'Deny' else 'None' END AS GRANTORDENY,
C.[NAME] AS FEATURENAME,
C.DESCRIPTION AS FEATUREDESCRIPTION,
C.SECURITYUIFOLDER,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SRP.DATEADDED,
SRP.DATECHANGED,
SRP.TSLONG
from dbo.SYSTEMROLEPERM_MAPENTITY AS SRP
join dbo.V_INSTALLED_MAPENTITYCATALOG as C on SRP.MAPENTITYCATALOGID=C.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SRP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SRP.CHANGEDBYID
UNION ALL
select
SRP.ID,
SRP.SYSTEMROLEID,
SRP.SEARCHLISTCATALOGID AS FEATUREID,
'Search List' as FEATURETYPE,
CASE SRP.GRANTORDENY WHEN 1 THEN 'Grant' WHEN 0 THEN 'Deny' else 'None' END AS GRANTORDENY,
C.[NAME] AS FEATURENAME,
C.DESCRIPTION AS FEATUREDESCRIPTION,
C.SECURITYUIFOLDER,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SRP.DATEADDED,
SRP.DATECHANGED,
SRP.TSLONG
from dbo.SYSTEMROLEPERM_SEARCHLIST AS SRP
join dbo.V_INSTALLED_SEARCHLISTCATALOG as C on SRP.SEARCHLISTCATALOGID=C.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SRP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SRP.CHANGEDBYID
UNION ALL
select
SRP.ID,
SRP.SYSTEMROLEID,
SRP.KPICATALOGID AS FEATUREID,
'KPI' as FEATURETYPE,
CASE SRP.GRANTORDENY WHEN 1 THEN 'Grant' WHEN 0 THEN 'Deny' else 'None' END AS GRANTORDENY,
C.[NAME] AS FEATURENAME,
C.DESCRIPTION AS FEATUREDESCRIPTION,
C.SECURITYUIFOLDER,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SRP.DATEADDED,
SRP.DATECHANGED,
SRP.TSLONG
from dbo.SYSTEMROLEPERM_KPI AS SRP
join dbo.V_INSTALLED_KPICATALOG as C on SRP.KPICATALOGID=C.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SRP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SRP.CHANGEDBYID
UNION ALL
select
SRP.ID,
SRP.SYSTEMROLEID,
SRP.REPORTCATALOGID AS FEATUREID,
'Report Parameter' as FEATURETYPE,
CASE SRP.GRANTORDENY WHEN 1 THEN 'Grant' WHEN 0 THEN 'Deny' else 'None' END AS GRANTORDENY,
C.[UINAME] AS FEATURENAME,
C.DESCRIPTION AS FEATUREDESCRIPTION,
N'' as SECURITYUIFOLDER,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SRP.DATEADDED,
SRP.DATECHANGED,
SRP.TSLONG
from dbo.SYSTEMROLEPERM_REPORT AS SRP
join dbo.V_INSTALLED_REPORTCATALOG as C on SRP.REPORTCATALOGID=C.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SRP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SRP.CHANGEDBYID
UNION ALL
select
SRP.ID,
SRP.SYSTEMROLEID,
SRP.QUERYVIEWCATALOGID AS FEATUREID,
'Query View' as FEATURETYPE,
CASE SRP.GRANTORDENY WHEN 1 THEN 'Grant' WHEN 0 THEN 'Deny' else 'None' END AS GRANTORDENY,
C.[DISPLAYNAME] AS FEATURENAME,
C.DESCRIPTION AS FEATUREDESCRIPTION,
C.SECURITYUIFOLDER,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SRP.DATEADDED,
SRP.DATECHANGED,
SRP.TSLONG
from dbo.SYSTEMROLEPERM_QUERYVIEW AS SRP
join dbo.V_INSTALLED_QUERYVIEWCATALOG as C on SRP.QUERYVIEWCATALOGID=C.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SRP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SRP.CHANGEDBYID
where cast(coalesce(C.QUERYVIEWSPEC.value('declare namespace bbfa="bb_appfx_queryview";/bbfa:QueryViewSpec[1]/@SecurityUIDisplayFeature','bit'), 1) as bit) = 1
UNION ALL
select
SRP.ID,
SRP.SYSTEMROLEID,
SRP.SYSTEMPRIVILEGECATALOGID AS FEATUREID,
'System Privilege' as FEATURETYPE,
CASE SRP.GRANTORDENY WHEN 1 THEN 'Grant' WHEN 0 THEN 'Deny' else 'None' END AS GRANTORDENY,
C.[NAME] AS FEATURENAME,
C.DESCRIPTION AS FEATUREDESCRIPTION,
C.SECURITYUIFOLDER,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SRP.DATEADDED,
SRP.DATECHANGED,
SRP.TSLONG
from dbo.SYSTEMROLEPERM_SYSTEMPRIVILEGE AS SRP
join dbo.SYSTEMPRIVILEGECATALOG as C on SRP.SYSTEMPRIVILEGECATALOGID=C.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SRP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SRP.CHANGEDBYID
;