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.
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?
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'. */createoraltertrigger"model_parameter_td"on"model_parameter"fordeleteasbegin
/* Ifnorows were modified exit trigger */
if @@rowcount = 0
return
ifnotexists(select1from deleted)
return
-- Donotcount affected rowsfor 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 parametersselect1 -- aangepast ivm it-984update 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
endgo
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?
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.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.