

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/TM_SecurityCheckForTask.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
-- rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/TM_SecurityCheckForTask.sp,v $ $Id: TM_SecurityCheckForTask.sp,v 1.11.2.31.28.2 2021/06/10 04:17:05 rengu Exp $";
-- ===================================================================================
-- Author:      Sergio Bonilla
-- Create date: 08/28/2021
-- Description: Check the security rights for user to create/modify task.
-- ===================================================================================
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='TM_SecurityCheckForTask')
BEGIN
	print '>>> Drop Stored Procedure: TM_SecurityCheckForTask <<<'
	drop procedure TM_SecurityCheckForTask
END
IF EXISTS (select * from GxQscripts where name='TM_SecurityCheckForTask')
	delete from GxQscripts where name = 'TM_SecurityCheckForTask'
GO

IF EXISTS (select * from GXDBVersions where aliasname='TM_SecurityCheckForTask')
	delete from GXDBVersions where aliasname = 'TM_SecurityCheckForTask'
GO
print '... Creating Procedure: TM_SecurityCheckForTask'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure TM_SecurityCheckForTask
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
--
--   PARAMETERS   &   OUTPUTS
--
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  @userId integer,
  @localeId_in integer,
  @operation integer = 0,
  @xmlText XML OUTPUT
AS
BEGIN
SET NOCOUNT ON
	DECLARE @clientId		INT
	DECLARE @clientName		NVARCHAR(MAX)
	DECLARE @clientGroupId		INT
	DECLARE @clientGroupName	NVARCHAR(MAX)
	DECLARE @dataSourceName NVARCHAR(MAX)
	DECLARE @planName 		NVARCHAR(MAX)
	DECLARE @userName		NVARCHAR(1024) = ''
	DECLARE @errorCode		INT = 0
	DECLARE @errorString	NVARCHAR(MAX) = ''
	DECLARE @objectName		NVARCHAR(1024) = ''
	DECLARE @taskName		NVARCHAR(MAX)
	DECLARE @oldTaskName	NVARCHAR(MAX)
	DECLARE @subTaskName	NVARCHAR(MAX)
	DECLARE @description	NVARCHAR(MAX) = ''
	DECLARE @oldDescription	NVARCHAR(MAX) = ''
	DECLARE @capabilities	BIGINT = 0
	DECLARE @capability		INT = 0
	DECLARE @storagePolicyId	INT
	DECLARE @storagePolicyName	NVARCHAR(MAX) = ''
	DECLARE @taskType		INT = 0
	DECLARE @subTaskType	INT = 0
	DECLARE @operationType	INT = 0
	DECLARE @policyType		INT = 0
	DECLARE @taskId			INT = 0
	DECLARE @subTaskId		INT = 0
	DECLARE @hasAssocPolicyRights	INT = 0
	DECLARE @hasEditPolicyRights	INT = 0
	DECLARE @isModify		INT = 0
	DECLARE @hasAssociationTag		INT = 0
	DECLARE @hasEditElements		INT = 0
	DECLARE @noAssociation			INT = 0		--ASSOCIATION TAG COULD BE SENT BUT NOT HAVE ANY ENTITIES IN IT.  NEED TO CHECK FOR SUCH CASES
	DECLARE @taskOperation			INT = 0
	DECLARE @hasAdminAssocAtCSLevel	INT = 0
	DECLARE @subTaskOperationType   INT = 0
	DECLARE @subClientBasedAnalytics INT = 0
DECLARE @_EV_SCHEDULE_ADMIN			INT = POWER(2, 1-1)
DECLARE @_EV_MANAGE_APPLICATION		INT = POWER(2, 2-1)
DECLARE @_EV_SCHEDULE_APPLICATION	INT = POWER(2, 3-1)
DECLARE @_EV_MANAGE_ARCHIVES		INT = POWER(2, 4-1)
DECLARE @_EV_MANAGE_INSTALLATION	INT = POWER(2,7-1)
DECLARE @_EV_MANAGE_LIBRARY			INT = POWER(2, 8-1)
DECLARE @_E_V_MANAGE_LICENSE		INT = POWER(2, 9-1)
DECLARE @_E_V_MANAGE_MEDIAAGENT		INT = POWER(2, 10-1)
DECLARE @_EV_ADMIN_LIBRARY			INT = POWER(2, 11-1)
DECLARE @_EV_BACKUP					INT = POWER(2, 12-1)
DECLARE @_EV_BROWSE					INT = POWER(2, 13-1)
DECLARE @_EV_MANAGE_USER			INT = POWER(2, 14-1)
DECLARE @_EV_RUN_REPORTS			INT = POWER(2, 15-1)
DECLARE @_EV_MANAGE_OPERATIONS		INT = POWER(2, 16-1)
	DECLARE @assocType					INT
	DECLARE @isCsLevelAssociation 		INT = 0
	DECLARE @csLevelCapabilities 		BIGINT = 0
--
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--
	SET @localeId_in = ISNULL (( SELECT ref.value('@localeId', 'INT')
					FROM @xmlText.nodes ('//locale') R(ref)), @localeId_in)
	--SET @userId = ISNULL (( SELECT ref.value('@userId', 'INT')
	--				FROM @xmlText.nodes ('//user') R(ref)), @userId)
	SET @taskType = ISNULL (( SELECT ref.value('@taskType', 'INT')
FROM @xmlText.nodes ('//taskInfo/task') R(ref)), 1)
	SET @taskId = ISNULL (( SELECT ref.value('@taskId', 'INT')
					FROM @xmlText.nodes ('//taskInfo/task/task') R(ref)), @taskId)
	SET @taskId = ISNULL (( SELECT ref.value('@taskId', 'INT')
					FROM @xmlText.nodes ('//taskInfo/task') R(ref)), @taskId)
	SET @subTaskId = ISNULL (( SELECT ref.value('@subTaskId', 'INT')
					FROM @xmlText.nodes ('//subTasks[1]/subTask[1]') R(ref)), @subTaskId)
	SET @taskName = ISNULL (( SELECT ref.value('@taskName', 'NVARCHAR(MAX)')
					FROM @xmlText.nodes ('//taskInfo/task/task') R(ref)), @taskName)
	SET @taskName = ISNULL (( SELECT ref.value('@taskName', 'NVARCHAR(MAX)')
					FROM @xmlText.nodes ('//taskInfo/task') R(ref)), @taskName)
	SET @subTaskName = ISNULL (( SELECT ref.value('@subTaskName', 'NVARCHAR(MAX)')
					FROM @xmlText.nodes ('//subTasks[1]/subTask[1]') R(ref)), @subTaskName)
	SET @subTaskType = ISNULL (( SELECT ref.value('@subTaskType', 'INT')
					FROM @xmlText.nodes ('//subTasks[1]/subTask[1]') R(ref)), @subTaskType)
	SET @subTaskOperationType = ISNULL (( SELECT ref.value('@operationType', 'INT')
FROM @xmlText.nodes ('//subTasks[1]/subTask[1]') R(ref)), 2 )
	SET @subClientBasedAnalytics = ISNULL (( SELECT ref.value('(options/adminOpts/contentIndexingOption/@subClientBasedAnalytics)[1]', 'INT')
					FROM @xmlText.nodes ('//subTasks[1]') R(ref)), 0 )
	SET @description = ISNULL (( SELECT ref.value('@description', 'NVARCHAR(MAX)')
					FROM @xmlText.nodes ('//taskInfo/task') R(ref)), @description)
	SET @policyType = ISNULL (( SELECT ref.value('@policyType', 'INT')
					FROM @xmlText.nodes ('//taskInfo/task') R(ref)), @policyType)
	SET @taskOperation = ISNULL (( SELECT ref.value('@taskOperation', 'INT')
FROM @xmlText.nodes ('//taskInfo') R(ref)), 1)
	SET @isModify = @xmlText.exist('/TMMsg_ModifyTaskReq')
	SET @hasAssociationTag = @xmlText.exist('//taskInfo/associations')
	IF @xmlText.exist('//subTasks/subTask') = 1 OR @xmlText.exist('//appGroup/appGroups') = 1 OR @xmlText.exist('//appGroup/appTypes') = 1
		SET @hasEditElements = 1
--
	IF OBJECT_ID('tempdb.dbo.#AssocEntity') IS NOT null
		DROP TABLE #AssocEntity
	IF OBJECT_ID('tempdb.dbo.#EntityRights') IS NOT null
		DROP TABLE #EntityRights
	IF OBJECT_ID('tempdb.dbo.#AdminEntityRights') IS NOT null
		DROP TABLE #AdminEntityRights
	IF object_id('tempdb.dbo.#entitiesWithViewSchedCapability') is not null
		DROP TABLE #entitiesWithViewSchedCapability
	IF OBJECT_ID('tempdb.dbo.#entitiesWithEditPermission')   IS NOT NULL
		DROP TABLE #entitiesWithEditPermission
	CREATE TABLE #entitiesWithEditPermission(entityId INT NOT NULL)
	CREATE CLUSTERED INDEX #entitiesWithEditPermission_entityId_idx ON #entitiesWithEditPermission(entityId)
--
	CREATE TABLE #AssocEntity (operationType INT, commCellId INT default 2, clientGroupId INT default 0, clientId INT default 0, appTypeId INT default 0, instanceId INT default 0, backupsetId INT default 0, subClientId INT default 0,
								mediaAgentId INT default 0, libraryId INT default 0, archGroupId INT default 0, policyType INT default 0, exclude INT default 0, assocType INT default 0, entityId INT default 0)
create table  #entitiesWithViewSchedCapability ( [capabilities]  bigint	NOT null, [flag]		  int NOT null default 0, [commCellId]    int	NOT null default 2, [clientGroupId] int	NOT null default 0, [clientId]      int	NOT null default 0, [appTypeId]     int	NOT null default 0, [instanceId]    int	NOT null default 0, [backupsetId]   int	NOT null default 0, [subClientId]   int	NOT null default 0, [mediaAgentId]  int	NOT null default 0, [libraryId]     int	NOT null default 0, [archGroupId]   int	NOT null default 0, [vtContainerId] int	NOT null default 0, [vtPolicyId]    int	NOT null default 0, [vtShelfId]     int	NOT null default 0, [reviewSetId]   int	NOT null default 0, [querySetId]	  int	NOT null default 0, [downloadSetId] int	NOT null default 0, [ermId]   	  int	NOT null default 0, [legalHoldId]   int	NOT null default 0, [tagId]		  int	NOT null default 0, [cdPolicyId]    int	NOT null default 0, [complianceReportId]   int	NOT null default 0, [taskId]        int	NOT null default 0, [workflowId]    int	NOT null default 0, [logMonitoringPolicyId] int	NOT null default 0, [arrayId]       int	NOT null default 0, [cloudId]       int	NOT null default 0, [userGroupId]   int	NOT null default 0, [providerId]    int	NOT null default 0, [entityId1]     int	NOT null default 0, [entityId2]     int	NOT null default 0, [entityId3]     int	NOT null default 0, [entityId4]     int	NOT null default 0, [entityId5]     int	NOT null default 0, [entityId6]     int	NOT null default 0, [entityId7]     int	NOT null default 0, [entityId8]     int	NOT null default 0, [entityId9]     int	NOT null default 0  )
	EXEC sec_getUserObjects @userId, 0, 0, '#entitiesWithViewSchedCapability', 1  -- MR:264340, passing getStaticNodeCapabilities=1 will include rows with <<entity>>Id = -10 in #entitiesWithViewSchedCapability when user has capabilities on all objects of an entityType, e.g.
	                                                                              -- #entitiesWithViewSchedCapability will have a row with clientId=-10 when user has permissions on all clients. We need this for all clients/group association check.
	-- In case policyType or taskType is not present, then pick it up from schedule policy if its present.
	if ( ( @policyType=0 OR @taskType = 1 ) AND @taskId>0)
	begin
		select @policyType=policyType, @taskType = taskType from TM_Task (NOLOCK)
		where taskId=@taskId
	end
	IF EXISTS(SELECT 1 FROM #entitiesWithViewSchedCapability  sec
		WHERE (sec.capabilities&@_EV_SCHEDULE_ADMIN <> 0) AND
sec.commCellId=2 AND sec.clientGroupId=0 AND sec.clientId=0 AND sec.appTypeId=0 AND sec.instanceId=0 AND sec.backupsetId=0 AND sec.subClientId=0 AND sec.mediaAgentId=0 AND sec.libraryId=0 AND sec.archGroupId=0 AND sec.vtContainerId=0 AND sec.vtPolicyId=0 AND sec.vtShelfId=0 AND sec.reviewSetId=0 AND sec.querySetId=0 AND sec.downloadSetId=0 AND sec.ermId=0 AND sec.legalHoldId=0 AND sec.tagId=0 AND sec.cdPolicyId=0 AND sec.complianceReportId=0 AND sec.taskId=0 AND sec.workflowId=0 AND sec.logMonitoringPolicyId=0 AND sec.arrayId=0 AND sec.cloudId=0 AND sec.userGroupId=0 AND sec.providerId=0 AND sec.entityId1=0 AND sec.entityId2=0 AND sec.entityId3=0 AND sec.entityId4=0 AND sec.entityId5=0									AND sec.entityId6=0 AND sec.entityId7=0 AND sec.entityId8=0 AND sec.entityId9=0
	) -- USER HAS ASSOCIATION AT CC LEVEL
		SET @hasAdminAssocAtCSLevel = 1
--
IF @xmlText.exist('//subTasks/options/backupOpts/vsaBackupOptions/selectiveVMInfo/@vmGuid') = 1 AND @taskType = 1 AND @subTaskType = 2
	BEGIN
		INSERT INTO #AssocEntity
		(operationType, clientId)
		SELECT DISTINCT st.value('@operationType','INT') operationType, P.VMclientId clientId
		FROM
		@xmlText.nodes('//subTasks/options/backupOpts/vsaBackupOptions/selectiveVMInfo') AS O(op)
		CROSS APPLY @xmlText.nodes('//subTasks/subTask') AS S(st)
		JOIN APP_VMProp P (NOLOCK) ON P.attrName = 'vmGuid' AND P.attrVal=op.value('@vmGuid','VARCHAR(50)')
	END
	ELSE
	BEGIN
	INSERT INTO #AssocEntity
	(operationType, commCellId, clientGroupId, clientId, appTypeId, instanceId, backupsetId, subClientId, mediaAgentId, libraryId, archGroupId, policyType, exclude, assocType, entityId)
SELECT CASE WHEN ASS.operationType is null THEN 2 ELSE ASS.operationType END [operationType],
		CASE WHEN ASS.commCellId is null THEN 2 ELSE ASS.commCellId END [commCellId],
		CASE WHEN ASS.clientGroupId is null AND (ASS.operationType is not null OR @policyType = 0) THEN 0 ELSE ASS.clientGroupId END [clientGroupId],
		CASE WHEN ASS.clientId is null AND (ASS.operationType is not null OR @policyType = 0) THEN 0 ELSE ASS.clientId END [clientId],
		CASE WHEN ASS.applicationId is null THEN 0 ELSE ASS.applicationId END [appTypeId],
		CASE WHEN ASS.instanceId is null THEN 0 ELSE ASS.instanceId END [instanceId],
		CASE WHEN ASS.backupsetId is null THEN 0 ELSE ASS.backupsetId END [backupsetId],
		CASE WHEN ASS.subclientId is null THEN 0 ELSE ASS.subclientId END [subClientId],
		CASE WHEN ASS.mediaAgentId is null THEN 0 ELSE ASS.mediaAgentId END [mediaAgentId],
		CASE WHEN ASS.libraryId is null THEN 0 ELSE ASS.libraryId END [libraryId],
		CASE WHEN ASS.storagePolicyId is null THEN 0 ELSE ASS.storagePolicyId END [archGroupId],
		ASS.policyType, ASS.exclude, ASS.assocType, ASS.entityId
	FROM
	(
		-- WE WANT TO USE GA.clientGroupId BECAUSE IT WILL BE NULL IF CLIENT GROUP IS EMPTY AND ADMIN _SECURITY CHECKS FOR VALUE OF 0 BELOW
		SELECT BA.operationType, GA.id AS clientGroupId, BA.applicationId, BA.backupsetId, BA.clientId AS clientId,
BA.commCellId, BA.instanceId, BA.libraryId, BA.mediaAgentId, BA.storagePolicyId, BA.subclientId, 0 policyType, exclude, assocType, entityId
		FROM
		(
			SELECT @subTaskOperationType operationType, ref.value('@commCellId','INT') commCellId, ref.value('@clientGroupId','INT') clientGroupId, ref.value('@clientId', 'INT') clientId,
				ref.value('@applicationId','INT') applicationId, ref.value('@instanceId', 'INT') instanceId, ref.value('@backupsetId','INT') backupsetId, ref.value('@subclientId', 'INT') subclientId,
				ref.value('@mediaAgentId','INT') mediaAgentId, ref.value('@libraryId', 'INT') libraryId, ref.value('@storagePolicyId', 'INT') storagePolicyId, ref.value('@ _type_', 'INT') assocType,
CASE WHEN ref.value('@ _type_', 'INT') = 132 THEN ref.value('@seaDataSourceId','INT')
WHEN ref.value('@ _type_', 'INT') = 158 THEN ref.value('@planId','INT') ELSE 0 END entityId,
				ref.value('flags[1]/@exclude','INT') exclude
			FROM
			@xmlText.nodes('//taskInfo/associations') AS T(ref)
WHERE @policyType IN (0) OR @subTaskOperationType in (1, 2, 3, 4, 4034)
			-- It could be offline content indexing job at subclient level or CONTENT_INDEX_FROM_SUBCLIENT.
OR (@subTaskOperationType = 4022 AND @subClientBasedAnalytics = 1 )
OR @subTaskOperationType = 5020
OR @subTaskOperationType = 5023
OR @subTaskOperationType = 5025
		) BA
		LEFT OUTER JOIN APP_ClientGroup GA WITH (NOLOCK) ON GA.id = BA.clientGroupId
		UNION ALL
		SELECT BA.operationType, GA.id AS clientGroupId, BA.applicationId, BA.backupsetId, BA.clientId AS clientId,
BA.commCellId, BA.instanceId, BA.libraryId, BA.mediaAgentId, BA.storagePolicyId, BA.subclientId, 0 policyType, exclude, assocType, 0
		FROM
		(
			SELECT @subTaskOperationType operationType, ref.value('@commCellId','INT') commCellId, ref.value('@clientGroupId','INT') clientGroupId, ref.value('@clientId', 'INT') clientId,
				ref.value('@applicationId','INT') applicationId, ref.value('@instanceId', 'INT') instanceId, ref.value('@backupsetId','INT') backupsetId, ref.value('@subclientId', 'INT') subclientId,
				ref.value('@mediaAgentId','INT') mediaAgentId, ref.value('@libraryId', 'INT') libraryId, ref.value('@storagePolicyId', 'INT') storagePolicyId, ref.value('@ _type_', 'INT') assocType,
				ref.value('flags[1]/@exclude','INT') exclude
			FROM
			@xmlText.nodes('//adminOpts/updateOption/clientAndClientGroups') AS T(ref)
WHERE @subTaskOperationType in (4020)
		) BA
		LEFT OUTER JOIN APP_ClientGroup GA WITH (NOLOCK) ON GA.id = BA.clientGroupId
		UNION ALL
		SELECT BA.operationType, null AS clientGroupId, BA.applicationId, BA.backupsetId, BA.clientId AS clientId,
			BA.commCellId, BA.instanceId, BA.libraryId, BA.mediaAgentId, BA.storagePolicyId, BA.subclientId, BA.policyType, exclude, assocType, 0
		FROM
		(
			SELECT @subTaskOperationType operationType, ref.value('@commCellId','INT') commCellId, ref.value('@clientGroupId','INT') clientGroupId, ref.value('@clientId', 'INT') clientId,
				ref.value('@applicationId','INT') applicationId, ref.value('@instanceId', 'INT') instanceId, ref.value('@backupsetId','INT') backupsetId, ref.value('@subclientId', 'INT') subclientId,
				ref.value('@mediaAgentId','INT') mediaAgentId, ref.value('@libraryId', 'INT') libraryId, ref.value('@storagePolicyId', 'INT') storagePolicyId, @policyType policyType,
				ref.value('@ _type_', 'INT') assocType, ref.value('flags[1]/@exclude','INT') exclude
			FROM
			@xmlText.nodes('//taskInfo/associations') AS A(ref)
WHERE @policyType IN (6, 1, 2, 3, 11)
		) BA
	) ASS
	END
--
	--REST BASE OR COMMAND LINE REQUEST MAY NOT POPULATE ALL THE ENTITIES. ADD MISSING ENTITIES FOR ERROR MESSAGE
	IF EXISTS (SELECT * FROM #AssocEntity
				WHERE
					(clientId = 0 AND (appTypeId > 0 OR instanceId > 0 OR backupsetId > 0 OR subClientId > 0)
					)
					OR
					(
						appTypeId = 0 AND (instanceId > 0 OR backupsetId > 0 OR subClientId > 0)
					)
					OR
					(
						instanceId = 0 AND (backupsetId > 0 OR subClientId > 0)
					)
					OR
					(
						backupsetId = 0 AND subClientId > 0
					)
				)
	BEGIN
		--ADD MISSING ENTITIES
		UPDATE #AssocEntity
		SET clientId = (SELECT DISTINCT clientId FROM APP_Application APP WITH (NOLOCK)
							WHERE
							(
								(APP.id = subClientId OR subClientId = 0)
								AND
								(APP.backupSet = backupsetId OR backupsetId = 0)
								AND
								(APP.instance = instanceId OR instanceId = 0)
								AND
								(APP.appTypeId = appTypeId OR appTypeId = 0)
							)
						)
						WHERE clientId = 0 AND (subClientId > 0 OR backupsetId > 0 OR instanceId > 0 OR appTypeId > 0)
		UPDATE #AssocEntity
		SET appTypeId = (SELECT DISTINCT appTypeId FROM APP_Application APP WITH (NOLOCK)
							WHERE
							(
								(APP.id = subClientId OR subClientId = 0)
								AND
								(APP.backupSet = backupsetId OR backupsetId = 0)
								AND
								(APP.instance = instanceId OR instanceId = 0)
							)
						)
						WHERE appTypeId = 0 AND (subClientId > 0 OR backupsetId > 0 OR instanceId > 0)
		UPDATE #AssocEntity
		SET instanceId = (SELECT DISTINCT instance FROM APP_Application APP WITH (NOLOCK)
							WHERE
							(
								(APP.id = subClientId OR subClientId = 0)
								AND
								(APP.backupSet = backupsetId OR backupsetId = 0)
							)
						)
						WHERE instanceId = 0 AND (subClientId > 0 OR backupsetId > 0)
		UPDATE #AssocEntity
		SET backupsetId = (SELECT DISTINCT backupSet FROM APP_Application APP WITH (NOLOCK)
							WHERE
							(
								(APP.id = subClientId OR subClientId = 0)
							)
						)
						WHERE backupsetId = 0 AND (subClientId > 0)
	END
	/*IF EXISTS (SELECT * FROM #AssocEntity WHERE clientId = 0 AND (backupsetId <> 0 OR subClientId <> 0))
	BEGIN
		MERGE #AssocEntity AS AE
		USING
		(
			SELECT APP.clientId, APP.appTypeId, APP.instance, APP.backupSet, APP.id
			FROM APP_Application APP WITH (NOLOCK)
			JOIN #AssocEntity ASS ON (ASS.backupsetId = APP.backupSet OR ASS.backupsetId = 0)
			AND (ASS.subClientId = APP.id OR ASS.subClientId = 0)
		) AS AP ON AP.id = AE.subclientId OR AP.backupset = AE.backupsetId
		WHEN MATCHED THEN
			UPDATE SET
			AE.clientId = AP.clientId;
	END*/
	--WHEN RUNNING POLICY DO NOT CHECK FOR RIGHTS ON THE EXCLUDED ONES
	IF @operation = 5 --TaskOpType.RUN
		DELETE #AssocEntity
		WHERE exclude=1
--
	IF NOT EXISTS (SELECT 1 FROM #AssocEntity)
		SET @noAssociation = 1
--
	--SECURITY_ CHECK FOR SCHEDULE POLICY
IF @taskType = 4 AND @operation != 5 --TaskOpType.RUN  --DON'T CHECK FOR POLICY RIGHTS WHEN RUNNING A SCHEDULE
	BEGIN
		IF @isModify = 1  OR @operation != 0
		BEGIN
			SELECT @oldTaskName = taskName, @oldDescription = description FROM TM_Task WHERE taskId = @taskId
			IF @oldTaskName <> @taskName OR @oldDescription <> @description
				SET @hasEditElements = 1
			--WHEN MODIFY TASK REQUEST CHECK FOR EDIT RIGHTS
			IF @hasEditElements = 1
			BEGIN
EXEC sec_checkPermissionOnEntity @userId, 116, @hasEditPolicyRights OUTPUT, 35, @taskId
				IF @hasEditPolicyRights = 0
					GOTO SECURITY_MSG
			END
			ELSE
				SET @hasEditPolicyRights = 1
			IF @hasAssociationTag = 1
			BEGIN
IF @taskOperation <> 1 AND @isModify = 1
				BEGIN
					--REMOVE ENTRIES WHICH HAVE NOT CHANGED TO AVOID GIVING ERROR FOR USERS WHO DO NOT SEE AND CANNOT CHANGE THE ENTITY WHEN EDITING THE POLICY
					DELETE #AssocEntity
					FROM
					(
						SELECT clientGroupId, clientId, apptypeId, instanceId, backupsetId, subclientId, MediaAgentId, libraryId, policyId, exclude
						FROM TM_AssocEntity WHERE taskId = @taskId
					) T
					WHERE T.clientGroupId = #AssocEntity.clientGroupId
					AND T.clientId = #AssocEntity.clientId
					AND T.apptypeId = #AssocEntity.appTypeId
					AND T.instanceId = #AssocEntity.instanceId
					AND T.backupsetId = #AssocEntity.backupsetId
					AND T.subclientId = #AssocEntity.subClientId
					AND T.MediaAgentId = #AssocEntity.mediaAgentId
					AND T.libraryId = #AssocEntity.libraryId
					AND T.policyId = #AssocEntity.archGroupId
					AND T.exclude = ISNULL(#AssocEntity.exclude, 0)
				END
				IF EXISTS(
					SELECT ISNULL(clientGroupId, 0), clientId, apptypeId, instanceId, backupsetId, subclientId, mediaAgentId, libraryId, archGroupId FROM #AssocEntity
					EXCEPT
					SELECT clientGroupId, clientId, apptypeId, instanceId, backupsetId, subclientId, MediaAgentId, libraryId, policyId FROM TM_AssocEntity (NOLOCK)
					WHERE taskId = @taskId
				)	--ASSOCIATION HAS BEEN ADDED
				OR EXISTS(
					SELECT clientGroupId, clientId, apptypeId, instanceId, backupsetId, subclientId, MediaAgentId, libraryId, policyId FROM TM_AssocEntity (NOLOCK)
					WHERE taskId = @taskId
					EXCEPT
					SELECT ISNULL(clientGroupId, 0), clientId, apptypeId, instanceId, backupsetId, subclientId, mediaAgentId, libraryId, archGroupId FROM #AssocEntity
				)	--ASSOCIATION HAS BEEN REMOVED
				BEGIN
EXEC sec_checkPermissionOnEntity @userId, 118, @hasAssocPolicyRights OUTPUT, 35, @taskId
					IF @hasAssocPolicyRights = 0
						GOTO SECURITY_MSG
				END
				ELSE
					SET @hasAssocPolicyRights = 1
			END
			ELSE
            BEGIN
                --WHEN GUI SENDS REQUEST WITH ASSOCIATION CHANGE, NO ASSOCIATION IS PASSED THEN ASSOCIATIONS ARE BEING REMOVED SO CHECK FOR EDIT ASSOCIATION RIGHTS
IF @taskOperation = 6 AND @hasAssociationTag = 0 AND EXISTS (SELECT 1 FROM TM_AssocEntity WHERE taskId = @taskId)
EXEC sec_checkPermissionOnEntity @userId, 118, @hasAssocPolicyRights OUTPUT, 35, @taskId
				ELSE
					SET @hasAssocPolicyRights = 1
            END
			--FOR SCHEDULE POLICIES ONLY CHECK FOR EDIT RIGHTS FOR ENABLE/DISABLE OPERATIONS
			IF @operation IN (1, 2)	--TaskOpType.ENABLE, TaskOpType.DISABLE
				GOTO SECURITY_MSG
		END
		ELSE
		BEGIN
EXEC Sec_userHasCapability @userId, 0, @hasAssocPolicyRights OUTPUT, 0, 0, '115'
			SET @hasEditPolicyRights = @hasAssocPolicyRights
		END
	END
	ELSE
	BEGIN
		SET @hasAssocPolicyRights = 1
		SET @hasEditPolicyRights = 1
	END
IF EXISTS(SELECT 1 FROM #AssocEntity WHERE assocType = 132 AND operationType = 5025)
	BEGIN
EXEC [dbo].[sec_getNonIdaObjectsForThisUser] @userId,132,202/*CAT_DATACUBE_EDIT*/,'#entitiesWithEditPermission'
	END
	-- If there is a plan association we only need to check edit schedule policy rights as edit plan is automatically assigned when giving edit plan
IF EXISTS(SELECT 1 FROM #AssocEntity WHERE assocType = 158 )
	BEGIN
		IF @hasEditPolicyRights = 1
			GOTO SECURITY_MSG
	END
	SELECT @csLevelCapabilities = capabilities , @isCsLevelAssociation = 1 FROM #entitiesWithViewSchedCapability sec
WHERE ( sec.commCellId=2 AND sec.clientGroupId=0 AND sec.clientId=0 AND sec.appTypeId=0 AND sec.instanceId=0 AND sec.backupsetId=0 AND sec.subClientId=0 AND sec.mediaAgentId=0 AND sec.libraryId=0 AND sec.archGroupId=0 AND sec.vtContainerId=0 AND sec.vtPolicyId=0 AND sec.vtShelfId=0 AND sec.reviewSetId=0 AND sec.querySetId=0 AND sec.downloadSetId=0 AND sec.ermId=0 AND sec.legalHoldId=0 AND sec.tagId=0 AND sec.cdPolicyId=0 AND sec.complianceReportId=0 AND sec.taskId=0 AND sec.workflowId=0 AND sec.logMonitoringPolicyId=0 AND sec.arrayId=0 AND sec.cloudId=0 AND sec.userGroupId=0 AND sec.providerId=0 AND sec.entityId1=0 AND sec.entityId2=0 AND sec.entityId3=0 AND sec.entityId4=0 AND sec.entityId5=0									AND sec.entityId6=0 AND sec.entityId7=0 AND sec.entityId8=0 AND sec.entityId9=0 )
--
	-- GET ASSOCIATIONS FOR WHICH USER HAS RIGHTS
	SELECT CHK.operationType, CHK.clientGroupId, CHK.clientId, CHK.appTypeId, CHK.instanceId, CHK.backupsetId, CHK.subClientId, CHK.assocType, CHK.entityId
	INTO #EntityRights
	FROM
	(
		--GET ENTITIES FOR WHICH USER HAS RIGHTS AND ARE PART OF THE TASK ASSOCIATION
		SELECT ASS.operationType, ASS.clientGroupId, ASS.clientId, ASS.appTypeId, ASS.instanceId, ASS.backupsetId, ASS.subClientId, ASS.assocType, ASS.entityId
		FROM
			(
				SELECT ASS.operationType,
					ASS.clientGroupId,
					ASS.clientId,
					ASS.appTypeId,
					ASS.instanceId,
					ASS.backupsetId,
					ASS.subClientId,
					ASS.assocType,
					ASS.entityId,
					ASS.commCellId,
					ASS.mediaAgentId,
					ASS.libraryId,
					ASS.archGroupId
				FROM #AssocEntity ASS
				JOIN #entitiesWithViewSchedCapability sec ON
				-- Removing check for CS level association here. Since capabilities could be split across levels. Some could be at CS level and some could be at client level. Just checking for CS level capabilities is incorrect.
				(
					--(EV_BACKUP OR EV_SCHEDULE_APPLICATION)
					(
						(
							(((@csLevelCapabilities | capabilities)&@_EV_BACKUP) <> 0)
							AND
(((@csLevelCapabilities | capabilities)&@_EV_SCHEDULE_APPLICATION) <> 0 OR @taskType IN (1, 6))
							AND
operationType in (1, 2, 3, 4, 4034, 4022, 5020, 5024, 5023, 5025 )
						)
						OR
						(
							(((@csLevelCapabilities | capabilities)&@_EV_MANAGE_INSTALLATION) <> 0)
							AND
(((@csLevelCapabilities | capabilities)&@_EV_SCHEDULE_APPLICATION) <> 0 OR @taskType = 1)
							AND
operationType = 4020
						)
					)
					AND
					(
						( --CHECK USER HAVING ASSOCIATION FOR THE ENTITY
							(
								(
									ASS.clientId = sec.clientId AND ASS.clientId > 0
									OR
									(
										ASS.clientId = 0 AND sec.clientGroupId = ASS.clientGroupId AND ASS.clientGroupId>0
									)
								)
								AND (ASS.appTypeId     = sec.appTypeId   OR (sec.appTypeId = 0 AND sec.clientId <> 0))
								AND (ASS.instanceId    = sec.instanceId  OR (sec.instanceId = 0 AND sec.clientId <> 0))
								AND (ASS.backupsetId   = sec.backupSetId OR (sec.backupSetId = 0 AND sec.clientId <> 0))
								AND (ASS.subclientId   = sec.subClientId OR (sec.subClientId = 0 AND sec.clientId <> 0))
							)
						)
						OR
(ASS.assocType = 2 AND sec.clientId = -10)
						OR
(ASS.assocType = 27 AND sec.clientGroupId = -10)
					)
				)
				-- Check for CC level capability seperately  for better query performance.
				UNION
                SELECT ASS.operationType,
					ASS.clientGroupId,
					ASS.clientId,
					ASS.appTypeId,
					ASS.instanceId,
					ASS.backupsetId,
					ASS.subClientId,
					ASS.assocType,
					ASS.entityId,
					ASS.commCellId,
					ASS.mediaAgentId,
					ASS.libraryId,
					ASS.archGroupId
                FROM #AssocEntity ASS
                WHERE
                --(EV_BACKUP OR EV_SCHEDULE_APPLICATION)
                    (
                        (
                            ((@csLevelCapabilities&@_EV_BACKUP) <> 0)
                            AND
((@csLevelCapabilities&@_EV_SCHEDULE_APPLICATION) <> 0 OR @taskType IN (1, 6))
                            AND
operationType in (1, 2, 3, 4, 4034, 4022, 5020, 5024, 5023, 5025)
                        )
                        OR
                        (
                            ((@csLevelCapabilities & @_EV_MANAGE_INSTALLATION) <> 0)
                            AND
((@csLevelCapabilities & @_EV_SCHEDULE_APPLICATION) <> 0 OR @taskType = 1)
                            AND
operationType = 4020
                        )
                    )
                    AND @isCsLevelAssociation = 1
				UNION
				SELECT ASS.operationType,
					ASS.clientGroupId,
					ASS.clientId,
					ASS.appTypeId,
					ASS.instanceId,
					ASS.backupsetId,
					ASS.subClientId,
					ASS.assocType,
					ASS.entityId,
					ASS.commCellId,
					ASS.mediaAgentId,
					ASS.libraryId,
					ASS.archGroupId
				FROM #AssocEntity ASS
				JOIN #entitiesWithEditPermission EP ON EP.entityId = ASS.entityId
WHERE ASS.assocType = 132 AND ASS.entityId > 0 AND ASS.operationType = 5025
				UNION
				SELECT ASS.operationType,
					ASS.clientGroupId,
					ASS.clientId,
					ASS.appTypeId,
					ASS.instanceId,
					ASS.backupsetId,
					ASS.subClientId,
					ASS.assocType,
					ASS.entityId,
					ASS.commCellId,
					ASS.mediaAgentId,
					ASS.libraryId,
					ASS.archGroupId
				FROM #AssocEntity ASS
				JOIN #entitiesWithEditPermission EP ON EP.entityId = ASS.entityId
WHERE ASS.assocType = 158 AND ASS.entityId > 0
			) ASS
		WHERE
		(
(ASS.assocType NOT IN (132,158) OR ASS.assocType IS NULL)
			AND
			(
				ASS.clientId > 0
				OR ASS.clientGroupId > 0
				OR --9.0 UPDATE SCHEDULES HAVE ASSOCIATION AT CS LEVEL
				(
					ASS.commCellId > 0
					AND ASS.mediaAgentId = 0
					AND ASS.libraryId = 0
					AND ASS.archGroupId = 0
				)
			)
			OR
			(
ASS.assocType IN (132, 158)
				AND ASS.entityId > 0
			)
		)  --ONLY CHECK FOR CLIENT ENTITIES
	) CHK	--RESULT SET OF ASSOCIATIONS FOR WHICH USER HAS RIGHTS
IF NOT EXISTS(SELECT * FROM #AssocEntity WHERE policyType in (6, 1, 2, 3, 11))
	BEGIN
		SELECT TOP 1 @clientId=FAIL.clientId, @clientName=C.name, @clientGroupId = FAIL.clientGroupId, @clientGroupName = CG.name, @operationType=FAIL.operationType, @assocType = FAIL.assocType, @dataSourceName = DS.DataSourceName
		FROM
		(
			(	--CHECK FOR ENTITIES ADDED
				SELECT ASS.operationType, ASS.clientGroupId, ASS.clientId, ASS.appTypeId, ASS.instanceId, ASS.backupsetId, ASS.subClientId, ASS.assocType, ASS.entityId
				FROM #AssocEntity ASS
				EXCEPT	-- COMPARE ASSOCIATION WITH ENTITIES FOR WHICH USER HAS RIGHTS
				-- GET ASSOCIATIONS FOR WHICH USER HAS RIGHTS
				SELECT CHK.operationType, CHK.clientGroupId, CHK.clientId, CHK.appTypeId, CHK.instanceId, CHK.backupsetId, CHK.subClientId, CHK.assocType, CHK.entityId
				FROM #EntityRights CHK
			)
			UNION
			(	--CHECK FOR ENTITIES REMOVED
				SELECT CHK.operationType, CHK.clientGroupId, CHK.clientId, CHK.appTypeId, CHK.instanceId, CHK.backupsetId, CHK.subClientId, CHK.assocType, CHK.entityId
				FROM #EntityRights CHK
				EXCEPT	-- COMPARE ASSOCIATION WITH ENTITIES FOR WHICH USER HAS RIGHTS
				-- GET ASSOCIATIONS FOR WHICH USER HAS RIGHTS
				SELECT ASS.operationType, ASS.clientGroupId, ASS.clientId, ASS.appTypeId, ASS.instanceId, ASS.backupsetId, ASS.subClientId,ASS.assocType, ASS.entityId
				FROM #AssocEntity ASS
			)
		) FAIL	--RESULT SET CONTAINING ANY ASSOCIATION WHICH FAILED THE _SECURITY CHECK
		LEFT OUTER JOIN APP_Client C WITH (NOLOCK) ON C.id = FAIL.clientId
		LEFT OUTER JOIN APP_ClientGroup CG WITH (NOLOCK) ON CG.id = FAIL.clientGroupId
LEFT OUTER JOIN SEDataSource DS WITH (NOLOCK) ON DS.DataSourceId = FAIL.entityId AND FAIL.assocType = 132
LEFT OUTER JOIN APP_Plan PL WITH(NOLOCK) ON PL.id = FAIL.entityId AND FAIL.assocType = 158
	END
--
	SELECT CHK.operationType, CHK.archGroupId, CHK.policyType
	INTO #AdminEntityRights
	FROM
	(
		--GET ENTITIES FOR WHICH USER HAS RIGHTS AND ARE PART OF THE TASK ASSOCIATION
		SELECT DISTINCT ASS.operationType, ASS.archGroupId, ASS.policyType
		FROM #AssocEntity ASS
		JOIN #entitiesWithViewSchedCapability sec ON
		(
			--(EV_MANAGE_ARCHIVES)
			(
((capabilities&@_EV_MANAGE_ARCHIVES) <> 0) AND policyType in (6, 1, 2, 3, 11)
			)
			AND
			(
				( --CHECK USER HAVING ASSOCIATION FOR THE ENTITY
					(
						ASS.archGroupId = sec.archGroupId
						AND
						sec.archGroupId > 0
					)
					OR --CHECK FOR USER HAVING ASSOCIATION AT CC LEVEL
					(
sec.commCellId=2 AND sec.clientGroupId=0 AND sec.clientId=0 AND sec.appTypeId=0 AND sec.instanceId=0 AND sec.backupsetId=0 AND sec.subClientId=0 AND sec.mediaAgentId=0 AND sec.libraryId=0 AND sec.archGroupId=0 AND sec.vtContainerId=0 AND sec.vtPolicyId=0 AND sec.vtShelfId=0 AND sec.reviewSetId=0 AND sec.querySetId=0 AND sec.downloadSetId=0 AND sec.ermId=0 AND sec.legalHoldId=0 AND sec.tagId=0 AND sec.cdPolicyId=0 AND sec.complianceReportId=0 AND sec.taskId=0 AND sec.workflowId=0 AND sec.logMonitoringPolicyId=0 AND sec.arrayId=0 AND sec.cloudId=0 AND sec.userGroupId=0 AND sec.providerId=0 AND sec.entityId1=0 AND sec.entityId2=0 AND sec.entityId3=0 AND sec.entityId4=0 AND sec.entityId5=0									AND sec.entityId6=0 AND sec.entityId7=0 AND sec.entityId8=0 AND sec.entityId9=0
					)
				)
			)
		)
		--MR:150958 - FIXED FOR SCHEDULE POLICY WITH ALL ASSOCIATIONS
		--WHERE
		--	ASS.archGroupId > 0
	) CHK	--RESULT SET OF ASSOCIATIONS FOR WHICH USER HAS RIGHTS
IF EXISTS(SELECT * FROM #AssocEntity WHERE policyType in (6, 1, 2, 3, 11))
	BEGIN
		SELECT @operationType=FAIL.operationType, @storagePolicyId=FAIL.archGroupId, @storagePolicyName=G.name, @policyType=FAIL.policyType
		FROM
		(
			(	--CHECK FOR ENTITIES ADDED
				SELECT ASS.operationType, ASS.archGroupId, ASS.policyType
				FROM #AssocEntity ASS
				EXCEPT	-- COMPARE ASSOCIATION WITH ENTITIES FOR WHICH USER HAS RIGHTS
				SELECT CHK.operationType, CHK.archGroupId, CHK.policyType
				FROM #AdminEntityRights CHK
			)
			UNION
			(	--CHECK FOR ENTITIES REMOVED
				SELECT CHK.operationType, CHK.archGroupId, CHK.policyType
				FROM #AdminEntityRights CHK
				EXCEPT	-- COMPARE ASSOCIATION WITH ENTITIES FOR WHICH USER HAS RIGHTS
				SELECT ASS.operationType, ASS.archGroupId, ASS.policyType
				FROM #AssocEntity ASS
			)
			-- GET ASSOCIATIONS FOR WHICH USER HAS RIGHTS
		) FAIL	--RESULT SET CONTAINING ANY ASSOCIATION
		LEFT OUTER JOIN archGroup G (NOLOCK) ON G.id = FAIL.archGroupId
	END
--
SECURITY_MSG:
	IF @clientId IS NOT null OR @clientGroupId IS NOT null OR @hasAssocPolicyRights = 0 OR @hasEditPolicyRights = 0
	BEGIN
SET @errorCode = (462 | (CAST(POWER(2, 24) AS BIGINT) * 35))
IF @assocType = 27
		BEGIN
SELECT @objectName = message FROM EvLocaleMsgs WITH (NOLOCK) WHERE messageId = (2819 | (CAST(POWER(2, 24) AS BIGINT) * 35)) AND [localeId] = @localeId_in
SELECT TOP 1 @capabilities = capabilities FROM #entitiesWithViewSchedCapability WHERE clientGroupId=-10
		END
ELSE IF @assocType = 2
		BEGIN
SELECT @objectName = message FROM EvLocaleMsgs WITH (NOLOCK) WHERE messageId = (2820 | (CAST(POWER(2, 24) AS BIGINT) * 35)) AND [localeId] = @localeId_in
SELECT TOP 1 @capabilities = capabilities FROM #entitiesWithViewSchedCapability WHERE clientId=-10
		END
		ELSE IF @clientId IS not null AND @clientId <> 0
		BEGIN
SET @objectName = (SELECT message FROM EvLocaleMsgs WITH (NOLOCK) WHERE messageId = (1007 | (CAST(POWER(2, 24) AS BIGINT) * 35)) AND [localeId] = @localeId_in)
			SET @objectName = @objectName + ' [' + @clientName + ']'
			SELECT TOP 1 @capabilities = capabilities FROM #entitiesWithViewSchedCapability WHERE clientId=@clientId
		END
		ELSE IF @clientGroupName IS NOT null AND (@hasAssocPolicyRights <> 0 OR @hasEditPolicyRights <> 0)	--FOR UPDATE SCHEDULE WITH ASSOCIATION AT CS LEVEL THE CLIENGROUP WILL ALSO BE 0
		BEGIN
SET @objectName = (SELECT message FROM EvLocaleMsgs WITH (NOLOCK) WHERE messageId = (1013 | (CAST(POWER(2, 24) AS BIGINT) * 35)) AND [localeId] = @localeId_in)
			SET @objectName = @objectName + ' [' + @clientGroupName + ']'
			SELECT TOP 1 @capabilities = capabilities FROM #entitiesWithViewSchedCapability WHERE clientGroupId=@clientGroupId
		END
		ELSE IF @hasEditPolicyRights = 0 OR @hasAssocPolicyRights = 0
		BEGIN
			SET @taskName = ISNULL((SELECT taskName FROM TM_Task (NOLOCK) WHERE taskId = @taskId), @taskName)
SET @objectName = (SELECT message FROM EvLocaleMsgs WITH (NOLOCK) WHERE messageId = (910 | (CAST(POWER(2, 24) AS BIGINT) * 35)) AND [localeId] = @localeId_in)
			SET @objectName = @objectName + ' [' + @taskName + ']'
			IF @hasEditPolicyRights = 0
				IF @isModify = 1 OR @operation IN (1, 2)	--TaskOpType.ENABLE, TaskOpType.DISABLE
SET @capabilities = 116	--_CAT_SCHEDULING_POLICY_EDIT
				ELSE
SET @capabilities = 115	--_CAT_SCHEDULING_POLICY_EDIT
			ELSE
SET @capabilities = 118	--_CAT_SCHEDULING_POLICY_ASSOCIATIONS_ADD_REMOVE
		END
		ELSE
		BEGIN
			SET @subTaskName = ISNULL((SELECT subTaskName FROM TM_SubTask (NOLOCK) WHERE subTaskId = @subTaskId), @subTaskName)
			IF @subTaskName = ''
			BEGIN
				SET @taskName = ISNULL((SELECT taskName FROM TM_Task (NOLOCK) WHERE taskId = @taskId), @taskName)
				SET @subTaskName = @taskName
			END
SET @objectName = (SELECT message FROM EvLocaleMsgs WITH (NOLOCK) WHERE messageId = (2366 | (CAST(POWER(2, 24) AS BIGINT) * 61)) AND [localeId] = @localeId_in)
			SET @objectName = @objectName + ' [' + @subTaskName + ']'
SET @capabilities = 3	--_EV_SCHEDULE_APPLICATION
		END
		SET @capabilities |= @csLevelCapabilities   -- include CS level capabilities as well
		SET @errorString = (SELECT message FROM EvLocaleMsgs WITH (NOLOCK) WHERE messageId = @errorCode AND [localeId] = @localeId_in)
		SELECT @capability =
			CASE WHEN (@capabilities&@_EV_SCHEDULE_APPLICATION=0)
				AND
				(
					(
@operationType IN (1, 2, 3, 4, 4034,4022 ,5020 ,5023 ,5025 )
						AND
@taskType IN (2, 4)
					)
					OR
					(
						@operation in (1, 2, 3) --TaskOpType.ENABLE, TaskOpType.DISABLE, TaskOpType.DELETE
					)
				)
THEN 3 --_EV_SCHEDULE_APPLICATION
WHEN (@capabilities&@_EV_BACKUP=0 AND @operationType in (1, 2, 3, 4, 4034,4022, 5020, 5023, 5025 ))
THEN 12	--_EV_BACKUP
WHEN (@capabilities&@_EV_MANAGE_INSTALLATION=0 AND @operationType = 4020)
THEN 7
WHEN @capabilities = 115		--_CAT_SCHEDULING_POLICY_CREATE
				THEN @capabilities
WHEN @capabilities = 116		--_CAT_SCHEDULING_POLICY_EDIT
				THEN @capabilities
WHEN @capabilities = 118	--_CAT_SCHEDULING_POLICY_ASSOCIATIONS_ADD_REMOVE
				THEN @capabilities
			ELSE	--IF ENTITY HAS NO CAPABILITIES THEN GIVE MESSAGE BASED ON OPERATION TYPE
CASE WHEN @taskType IN (2, 4)
THEN 3	--IF ENTRY IS MISSING AND SCHEDULE REQUEST ASSUME _EV_SCHEDULE_APPLICATION
WHEN @operationType in (1, 2, 3, 4, 4034, 4022,5020,5023,5025 )
THEN 12
				ELSE
7	--_EV_MANAGE_INSTALLATION
				END
			END
		SET @errorString = REPLACE(@errorString, '^1%s', (SELECT CASE WHEN name <> '' THEN name ELSE login END FROM UMUsers WITH (NOLOCK) WHERE id = @userId))
		SET @errorString = REPLACE(@errorString, '^2%s', dbo.fn_EvFormatEventMsgText(0, @localeId_in, (SELECT messageId FROM UMPermissions WITH (NOLOCK) WHERE id = @capability), 0, 2))
		SET @errorString = REPLACE(@errorString, '^3%s', @objectName)
	--GUIMSG_SECURITY_USER_CAPABILITY
	END
IF @planName IS NOT NULL AND @assocType = 158 AND @taskType = 4
	BEGIN
SET @errorCode = (3440 | (CAST(POWER(2, 24) AS BIGINT) * 35))
		SET @errorString = ISNULL(
                                   (SELECT  [Message]
                                    FROM    EvLocaleMsgs WITH(NOLOCK)
                                    WHERE       (LocaleId = @localeId_in)
AND (MessageID = (3440 | (CAST(POWER(2, 24) AS BIGINT) * 35)))),
                                    -- default value
                                    'Access denied')
	END
IF @policyType IN (6, 1, 2, 3, 11)
	BEGIN
		IF @storagePolicyId IS NOT null
		BEGIN
SET @errorCode = (462 | (CAST(POWER(2, 24) AS BIGINT) * 35))
			IF @storagePolicyName IS NOT null AND @storagePolicyId <> 0
			BEGIN
SET @objectName = (SELECT message FROM EvLocaleMsgs WITH (NOLOCK) WHERE messageId = (1005 | (CAST(POWER(2, 24) AS BIGINT) * 35)) AND [localeId] = @localeId_in)
				SET @objectName = @objectName + ' [' + @storagePolicyName + ']'
				SELECT TOP 1 @capabilities = capabilities FROM #entitiesWithViewSchedCapability WHERE archGroupId=@storagePolicyId
			END
			SET @errorString = (SELECT message FROM EvLocaleMsgs WITH (NOLOCK) WHERE messageId = @errorCode AND [localeId] = @localeId_in)
			SELECT @capability =
CASE WHEN (@capabilities&@_EV_MANAGE_ARCHIVES=0 AND @policyType in (6, 1, 2, 3, 11))
THEN 4	--_EV_MANAGE_ARCHIVES
ELSE 3    -- _EV_SCHEDULE_APPLICATION IF ENTRY IS MISSING ASSUME _EV_SCHEDULE_APPLICATION
				END
			SET @errorString = REPLACE(@errorString, '^1%s', (SELECT CASE WHEN name <> '' THEN name ELSE login END FROM UMUsers WITH (NOLOCK) WHERE id = @userId))
			SET @errorString = REPLACE(@errorString, '^2%s', dbo.fn_EvFormatEventMsgText(0, @localeId_in, (SELECT messageId FROM UMPermissions WITH (NOLOCK) WHERE id = @capability), 0, 2))
			SET @errorString = REPLACE(@errorString, '^3%s', @objectName)
		END
		ELSE IF @operation = 0  ----TaskOpType.NONE  ONLY CHECK DURING CREATE AND MODIFY REQUEST
		AND @hasAdminAssocAtCSLevel = 0
		--ONLY CHECK IF REQUEST HAS PASSED THE ASSOCIATION INFORMATION
		AND
		(
			(
				@hasAssociationTag = 1	--HAS ASSOCIATION ELEMENTS
				AND
				@noAssociation = 1		--ASSOCIATION ELEMENT IS EMPTY
			)
			OR	--REQUEST IS FROM GUI WHICH DOES NOT SEND THE ASSOCIATIONS TAG WHEN ALL ASSOCIATIONS ARE REMOVED
			(
@hasAssociationTag = 0 AND @taskOperation = 6
			)
			AND
			EXISTS (SELECT 1 FROM TM_AssocEntity WHERE taskId = @taskId)
		)
		BEGIN
			GOTO EMPTY_MSG
		END
	--GUIMSG_SECURITY_USER_CAPABILITY
	END
ELSE IF @policyType NOT IN (6, 1, 2, 3, 11)
	AND @operation = 0  --TaskOpType.NONE  ONLY CHECK DURING CREATE AND MODIFY REQUEST
	AND @hasAdminAssocAtCSLevel = 0 --USER DOES NOT HAVE ADMIN RIGHTS AT CS LEVEL
    AND @hasAssocPolicyRights = 0   --USER DOES NOT HAVE SCHEDULE POLICY EDIT RIGHTS
	--ONLY CHECK IF REQUEST HAS PASSED THE ASSOCIATION INFORMATION
	AND
	(
		(
			@hasAssociationTag = 1	--HAS ASSOCIATION ELEMENTS
			AND
			@noAssociation = 1		--ASSOCIATION ELEMENT IS EMPTY
		)
		OR	--REQUEST IS FROM GUI WHICH DOES NOT SEND THE ASSOCIATIONS TAG WHEN ALL ASSOCIATIONS ARE REMOVED
		(
@hasAssociationTag = 0 AND @taskOperation = 6
		)
		AND
		EXISTS (SELECT 1 FROM TM_AssocEntity WHERE taskId = @taskId)
	)
	BEGIN
EMPTY_MSG:
SET @errorCode = (462 | (CAST(POWER(2, 24) AS BIGINT) * 35))
IF @hasAssocPolicyRights = 0 AND @hasAssociationTag = 0 AND @taskOperation = 6
        BEGIN
			SET @taskName = ISNULL((SELECT taskName FROM TM_Task (NOLOCK) WHERE taskId = @taskId), @taskName)
SET @objectName = (SELECT message FROM EvLocaleMsgs WITH (NOLOCK) WHERE messageId = (910 | (CAST(POWER(2, 24) AS BIGINT) * 35)) AND [localeId] = @localeId_in)
			SET @objectName = @objectName + ' [' + @taskName + ']'
SET @capabilities = 118	--_CAT_SCHEDULING_POLICY_ASSOCIATIONS_ADD_REMOVE
		    SET @errorString = REPLACE(@errorString, '^1%s', (SELECT CASE WHEN name <> '' THEN name ELSE login END FROM UMUsers WITH (NOLOCK) WHERE id = @userId))
		    SET @errorString = REPLACE(@errorString, '^2%s', dbo.fn_EvFormatEventMsgText(0, @localeId_in, (SELECT messageId FROM UMPermissions WITH (NOLOCK) WHERE id = @capability), 0, 2))
		    SET @errorString = REPLACE(@errorString, '^3%s', @objectName)
        END
        ELSE
        BEGIN
SET @objectName = (SELECT message FROM EvLocaleMsgs WITH (NOLOCK) WHERE messageId = (1008 | (CAST(POWER(2, 24) AS BIGINT) * 35)) AND [localeId] = @localeId_in)
		SET @errorString = (SELECT message FROM EvLocaleMsgs WITH (NOLOCK) WHERE messageId = @errorCode AND [localeId] = @localeId_in)
SET @capability = 1
		SET @errorString = REPLACE(@errorString, '^1%s', (SELECT CASE WHEN name <> '' THEN name ELSE login END FROM UMUsers WITH (NOLOCK) WHERE id = @userId))
		SET @errorString = REPLACE(@errorString, '^2%s', dbo.fn_EvFormatEventMsgText(0, @localeId_in, (SELECT messageId FROM UMPermissions WITH (NOLOCK) WHERE id = @capability), 0, 2))
		SET @errorString = REPLACE(@errorString, '^3%s', @objectName)
        END
	END
EXIT_SP:
	SET @xmlText = (
		SELECT  1           as TAG,
				NULL        as Parent,
			@errorCode      as [TMMsg_GenericResp!1!errorCode],
			@errorString    as [TMMsg_GenericResp!1!errorMessage]
		FOR XML EXPLICIT
	)
--
	SELECT @xmlText
END
IF OBJECT_ID('tempdb.dbo.#AssocEntity') IS NOT null
	DROP TABLE #AssocEntity
IF OBJECT_ID('tempdb.dbo.#EntityRights') IS NOT null
	DROP TABLE #EntityRights
IF OBJECT_ID('tempdb.dbo.#AdminEntityRights') IS NOT null
	DROP TABLE #AdminEntityRights
IF object_id('tempdb.dbo.#entitiesWithViewSchedCapability') is not null
	DROP TABLE #entitiesWithViewSchedCapability
IF OBJECT_ID('tempdb.dbo.#entitiesWithEditPermission')   IS NOT NULL
	DROP TABLE #entitiesWithEditPermission
GO

IF EXISTS (select * from GxQscripts where name = 'TM_SecurityCheckForTask')
	delete from GxQscripts where name = 'TM_SecurityCheckForTask'
GO

IF EXISTS (select * from GXDBVersions where aliasname='TM_SecurityCheckForTask')
	delete from GXDBVersions where aliasname = 'TM_SecurityCheckForTask'
GO

insert into GXDBVersions values(2, 'TM_SecurityCheckForTask',  'v1.11.2.31.28.2', 'TM_SecurityCheckForTask', 'v1.11.2.31.28.2')
GO

