Skip to main content
Solved

Permission error on trigger


Forum|alt.badge.img+13

Users with full rights on a certain table get an error message about a trigger on that table:

Cannot find the object "table_td" because it does not exist or you do not have permissions.

The trigger does exist however and with an account with sysadmin rights this error doesn't occur.

What's a possible solution here?

Best answer by Roland

It turns out the problem was being caused by a disable/enable trigger operation inside a control procedure. So the issue wasn't with the trigger itself. Regular users don't have this specific right on the database and removing the operation from the procedure fixed the issue. Thanks all for the suggestions.

View original

5 replies

Forum|alt.badge.img+17
  • Moderator
  • 761 replies
  • April 17, 2025

I think there might be a problem with the code of the trigger itself. Can you post the trigger code here? And are you sure the trigger code on the database matches the code in the SF? 


Forum|alt.badge.img+13
  • Author
  • Hero
  • 149 replies
  • April 17, 2025

The code on the database matches with what's in SF. The trigger works when a sysadmin account on the database is used to run our application.

This is the code:

/* Create or alter delete trigger model_parameter_td to table 'model_parameter'. */

create or alter trigger "model_parameter_td"
on "model_parameter"
for delete
as
begin

   /* If no rows were modified exit trigger */
   if @@rowcount = 0
      return

   if not exists(select 1 from deleted)
      return

   -- Do not count affected rows for performance
   set nocount on;

    --control_proc_id:      trg_model_parameter
    --template_id:          status_verkoopafdeling
    --prog_object_item_id:  status_verkoopafdeling
    --template_description: Overgenomen van de inmiddels verwijderde procedure trg_model_parameter_status_verkoopafdeling

    -- Pas de status en verkoopafdeling van het model aan ahv wijzigingen in de parameters
    select 1 -- aangepast ivm it-984
    update m
    set    m.[status]         = ps1.parameter_status
         , m.verkoop_afdeling = ps2.parameter_verkoop_afdeling
    from   model m
    join   deleted d
    on     d.bedrijf_nummer = m.bedrijf_nummer
    and    d.model_code     = m.model_code
    outer apply ( select w1.omschrijving as parameter_status
                  from   model_parameter mp1
                  join   parameter_detail pd1
                  on     mp1.bedrijf_nummer      = pd1.bedrijf_nummer
                  and    mp1.parameter_id        = pd1.parameter_id
                  and    mp1.parameter_detail_id = pd1.parameter_detail_id
                  join   woordenlijst w1
                  on     pd1.vertaal_id = w1.vertaal_id
                  where  mp1.bedrijf_nummer = m.bedrijf_nummer
                  and    mp1.model_code     = m.model_code
                  and    mp1.parameter_id   = 17 -- status
                ) ps1
    outer apply ( select w2.omschrijving as parameter_verkoop_afdeling
                  from   model_parameter mp2
                  join   parameter_detail pd2
                  on     mp2.bedrijf_nummer      = pd2.bedrijf_nummer
                  and    mp2.parameter_id        = pd2.parameter_id
                  and    mp2.parameter_detail_id = pd2.parameter_detail_id
                  join   woordenlijst w2
                  on     pd2.vertaal_id = w2.vertaal_id
                  where  mp2.bedrijf_nummer = m.bedrijf_nummer
                  and    mp2.model_code     = m.model_code
                  and    mp2.parameter_id   = 16 -- verkoop_afdeling
                ) ps2
    where  isnull(m.[status]        ,'') <> ps1.parameter_status
    or     isnull(m.verkoop_afdeling,'') <> ps2.parameter_verkoop_afdeling

end
go

 


Forum|alt.badge.img+17
  • Moderator
  • 761 replies
  • April 17, 2025

I would assume the delete on model also fires a delete trigger (or a cascading delete) which also triggers a delete on the table (called table) causing this error due to either missing rights or some error in the trigger on model or table. Is this correct?

I just noticed its an update on model. ​@Roland I suspect that there is a trigger present on the model table. Can you tell me if this is the case?


Jeroen van den Belt
Administrator
Forum|alt.badge.img+9

@Roland Can you answer Erwin his question if there is a trigger on the model table? This can indeed be the cause of your problem.


Forum|alt.badge.img+13
  • Author
  • Hero
  • 149 replies
  • Answer
  • April 30, 2025

It turns out the problem was being caused by a disable/enable trigger operation inside a control procedure. So the issue wasn't with the trigger itself. Regular users don't have this specific right on the database and removing the operation from the procedure fixed the issue. Thanks all for the suggestions.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings