Добавление прав на чтение для ряда таблиц, завязанных на конкретном контрагенте

Connector.DBEngine.ExecuteCustomSQL("<имя процедуры>", Parameters);

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
 
CREATE procedure [dbo].[tsp_AddReadRightsToAccountHistory]
	@AccountID uniqueidentifier,
	@OwnerID uniqueidentifier
as
begin
	set nocount on
 
	declare @AuOwnerID uniqueidentifier
	declare @TaskID uniqueidentifier
	declare @ContractID uniqueidentifier
	declare @InvoiceID uniqueidentifier
	declare @OfferingMovementID uniqueidentifier
 
	if @AccountID is NULL OR @OwnerID is NULL return
 
	SET @AuOwnerID = (SELECT ID FROM tbl_AdminUnit
                          WHERE UserContactID = @OwnerID)
 
	IF @AuOwnerID IS NULL return
 
	-- Проставление доступа на чтение для задачи привязанных к указанному контрагенту
	declare c_Task cursor FOR
	  SELECT ID FROM tbl_Task
	  WHERE AccountID = @AccountID
 
	OPEN c_Task
	FETCH NEXT FROM c_Task INTO @TaskID
	WHILE @@FETCH_STATUS=0
	BEGIN
        INSERT INTO tbl_TaskRight
        (ID, RecordID, AdminUnitID, CanRead, CanWrite, CanDelete, CanChangeAccess)
        VALUES (NewID(), @TaskID, @AuOwnerID, 1, 0, 0, 0)		
		FETCH NEXT FROM c_Task INTO @TaskID
	END
	CLOSE c_Task
	DEALLOCATE c_Task
 
	-- Проставление доступа на чтение для всех договоров контрагента
	declare c_Contract cursor FOR
	  SELECT ID FROM tbl_Contract
	  WHERE CustomerID = @AccountID
 
	OPEN c_Contract
	FETCH NEXT FROM c_Contract INTO @ContractID
	WHILE @@FETCH_STATUS=0
	BEGIN
        INSERT INTO tbl_ContractRight
        (ID, RecordID, AdminUnitID, CanRead, CanWrite, CanDelete, CanChangeAccess)
        VALUES (NewID(), @ContractID, @AuOwnerID, 1, 0, 0, 0)		
		FETCH NEXT FROM c_Contract INTO @ContractID
	END
	CLOSE c_Contract
	DEALLOCATE c_Contract
 
	-- Проставление доступа на чтение для всех счетов контрагента
	declare c_Invoice cursor FOR
	  SELECT ID FROM tbl_Invoice
	  WHERE CustomerID = @AccountID
 
	OPEN c_Invoice
	FETCH NEXT FROM c_Invoice INTO @InvoiceID
	WHILE @@FETCH_STATUS=0
	BEGIN
        INSERT INTO tbl_InvoiceRight
        (ID, RecordID, AdminUnitID, CanRead, CanWrite, CanDelete, CanChangeAccess)
        VALUES (NewID(), @InvoiceID, @AuOwnerID, 1, 0, 0, 0)		
		FETCH NEXT FROM c_Invoice INTO @InvoiceID
	END
	CLOSE c_Invoice
	DEALLOCATE c_Invoice
 
	-- Проставление доступа на чтение для всех расходных накладных контрагента
	declare c_OffMov cursor FOR
	  SELECT ID FROM tbl_OfferingMovement
	  WHERE RecipientID = @AccountID
 
	OPEN c_OffMov
	FETCH NEXT FROM c_OffMov INTO @OfferingMovementID
	WHILE @@FETCH_STATUS=0
	BEGIN
        INSERT INTO tbl_OfferingMovementRight
        (ID, RecordID, AdminUnitID, CanRead, CanWrite, CanDelete, CanChangeAccess)
        VALUES (NewID(), @OfferingMovementID, @AuOwnerID, 1, 0, 0, 0)		
		FETCH NEXT FROM c_OffMov INTO @OfferingMovementID
	END
	CLOSE c_OffMov
	DEALLOCATE c_OffMov	
end

var Parameters = CreateSPParameters();
CreateSPParameter(Parameters, 'AccountID', pdtGUID, AccountID);
CreateSPParameter(Parameters, 'OwnerID', pdtGUID, OwnerID);
var SQLText = 'exec [tsp_AddReadRightsToAccountHistory] :AccountID, :OwnerID';
Connector.DBEngine.ExecuteCustomSQL(SQLText, Parameters);

  CREATE PROCEDURE .... WITH EXECUTE AS OWNER
  AS
    ...

CREATE PROCEDURE .... WITH EXECUTE AS 'dbo'

grant execute on [dbo].tsp_AddReadRightsToAccountHistory to public