UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBATCH
Returns a table of ConstituentIDs for which the user has been granted the batch according to the role security groups.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @APPUSERID | uniqueidentifier | IN | |
| @BATCHID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBATCH
(
@APPUSERID uniqueidentifier,
@BATCHID uniqueidentifier
)
returns TABLE as
/*
Returns a row for every constituent that the the user has rights to according to record access security.
This function is optimized for use from the Blackbaud.AppFx.Security.Catalog.ConstitRecordSecurityService
class which implements the RecordSecurity service for Constituent record security.
As such, it assumes that a check for DENY occurs outside this function
and also assumes that a check for if the user is ISSYSADMIN occurs outside
this function.
It also assumes a check for UFN_SECURITY_APPUSER_GRANTED_BATCH_IN_NONRACROLE
occurs outside this function. If that function returns true there is no need to join to this TVF.
*/
RETURN
(
select
CSAA.CONSTITUENTID AS ID
from
dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT as CSAA
WHERE
CSAA.CONSTIT_SECURITY_ATTRIBUTEID IN
(
select
SYSTEMROLEAPPUSERCONSTITUENTSECURITY.CONSTITUENTSECURITYATTRIBUTEID
from
dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE as SV
with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE_BATCHTEMPLATEID_APPUSERID))
inner join dbo.BATCH on BATCH.BATCHTEMPLATEID = SV.BATCHTEMPLATEID
inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SV.SYSTEMROLEID
inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY on SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
where
SV.APPUSERID = @APPUSERID and
BATCH.ID = @BATCHID and
SV.GRANTORDENY = 1 and
SV.RECORDSECURITYMODE = 2 and
SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID
union all
select
SYSTEMROLEAPPUSERCONSTITUENTSECURITY.CONSTITUENTSECURITYATTRIBUTEID
from
dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE as SV
inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHTYPECATALOGID = SV.BATCHTYPECATALOGID
inner join dbo.BATCH on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SV.SYSTEMROLEID
inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY on SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
where
SV.APPUSERID = @APPUSERID and
BATCH.ID = @BATCHID and
SV.GRANTORDENY = 1 and
SV.RECORDSECURITYMODE = 2 and
SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID
union all
select
SYSTEMROLEAPPUSERCONSTITUENTSECURITY.CONSTITUENTSECURITYATTRIBUTEID
from
dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE as SV
with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE_BATCHWORKFLOWSTATEID_APPUSERID))
inner join dbo.BATCH on BATCH.BATCHWORKFLOWSTATEID = SV.BATCHWORKFLOWSTATEID
inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SV.SYSTEMROLEID
inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY on SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
where
SV.APPUSERID=@APPUSERID AND
BATCH.ID=@BATCHID AND
SV.GRANTORDENY=1 and
SV.RECORDSECURITYMODE = 2 and
SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID
union all
select
SYSTEMROLEAPPUSERCONSTITUENTSECURITY.CONSTITUENTSECURITYATTRIBUTEID
from
dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHPROCESSOR as SV
with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATEPROCESSOR_BATCHTEMPLATEID_APPUSERID))
inner join dbo.BATCH on BATCH.BATCHTEMPLATEID = SV.BATCHTEMPLATEID
inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SV.SYSTEMROLEID
inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY on SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
where
SV.APPUSERID=@APPUSERID and
BATCH.ID=@BATCHID and
SV.GRANTORDENY=1 and
SV.RECORDSECURITYMODE = 2 and
SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID
union all
select
SYSTEMROLEAPPUSERCONSTITUENTSECURITY.CONSTITUENTSECURITYATTRIBUTEID
from
dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATEAPPROVER as SV
with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATEAPPROVER_BATCHTEMPLATEID_APPUSERID))
inner join dbo.BATCH on BATCH.BATCHTEMPLATEID = SV.BATCHTEMPLATEID
inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SV.SYSTEMROLEID
inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY on SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
where
SV.APPUSERID=@APPUSERID and
BATCH.ID=@BATCHID and
SV.GRANTORDENY=1 and
SV.RECORDSECURITYMODE = 2 and
SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID
)
UNION ALL
--Constits with no security attributes if the user in a role with security mode = 1
select
ID
from
dbo.CONSTITUENT
where exists
(
select
1
from
dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE as SV
with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE_BATCHTEMPLATEID_APPUSERID))
inner join dbo.BATCH on SV.BATCHTEMPLATEID = BATCH.BATCHTEMPLATEID
WHERE
SV.APPUSERID = @APPUSERID
AND BATCH.ID = @BATCHID
AND SV.GRANTORDENY = 1
AND SV.RECORDSECURITYMODE = 1
union all
select
1
from
dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE as SV
inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHTYPECATALOGID = SV.BATCHTYPECATALOGID
inner join dbo.BATCH on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
where
SV.APPUSERID = @APPUSERID
AND BATCH.ID = @BATCHID
AND SV.GRANTORDENY = 1
AND SV.RECORDSECURITYMODE = 1
union all
select
1
from
dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE as SV
with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE_BATCHWORKFLOWSTATEID_APPUSERID))
inner join dbo.BATCH on BATCH.BATCHWORKFLOWSTATEID = SV.BATCHWORKFLOWSTATEID
where
SV.APPUSERID=@APPUSERID
AND BATCH.ID=@BATCHID
AND SV.GRANTORDENY=1
AND SV.RECORDSECURITYMODE=1
union all
select
1
from
dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHPROCESSOR as SV
with (NOEXPAND,INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATEPROCESSOR_BATCHTEMPLATEID_APPUSERID))
inner join dbo.BATCH on BATCH.BATCHTEMPLATEID = SV.BATCHTEMPLATEID
where
SV.APPUSERID = @APPUSERID AND
BATCH.ID = @BATCHID AND
SV.GRANTORDENY = 1 AND
SV.RECORDSECURITYMODE = 1
union all
select
1
from
dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATEAPPROVER as SV
with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATEAPPROVER_BATCHTEMPLATEID_APPUSERID))
inner join dbo.BATCH on BATCH.BATCHTEMPLATEID = SV.BATCHTEMPLATEID
where
SV.APPUSERID = @APPUSERID AND
BATCH.ID = @BATCHID AND
SV.GRANTORDENY = 1 AND
SV.RECORDSECURITYMODE = 1
)
AND
ID NOT IN
(
select
CSAA.CONSTITUENTID
from
dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT as CSAA
)
)