Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Rights needed to drop a trigger and restore it.
Message
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Sécurité
Divers
Thread ID:
01200883
Message ID:
01200897
Vues:
18
The db_ddladmin role rights should be enough to drop a trigger.
You don't have to drop triggers but can disable them.
-- Disable all triggers
ALTER TABLE tablename DISABLE TRIGGER ALL 
...
ALTER TABLE tablename ENABLE TRIGGER ALL
-- specific trigger
ALTER TABLE tablename DISABLE TRIGGER triggername
...
ALTER TABLE tablename ENABLE TRIGGER triggername
>I have a bulk process to pull in data from old system. I need to disable triggers in two tables.
>
>I have done that in an SP that works from QA just fine. I also have DBO rights because I can :)
>
>My rights that are presented from the loader app give dllAdmin rights to the user, but my triggers are still present, and they fail on pulling in data that doesn't exist as of yet (older system that never had this data)
>
>
>
>Opening lines of SP:
>
>ALTER          PROCEDURE [dbo].[usp_System_LoadIncidents]
>AS
>
>TRUNCATE TABLE tb_SafetyIncident
>TRUNCATE TABLE tb_SafetyIncidentCause
>TRUNCATE TABLE tb_SafetyIncidentInvolvedPerson
>
>-- Drop Triggers
>if exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].[SafetyIncidentNextValSP]') 
and OBJECTPROPERTY(id, N'IsTrigger') = 1)
>drop trigger [dbo].[SafetyIncidentNextValSP]
>
>if exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].[SafetyIncidentInvolvedPersonNextValSP]') 
and OBJECTPROPERTY(id, N'IsTrigger') = 1)
>drop trigger [dbo].[SafetyIncidentInvolvedPersonNextValSP]
--sb--
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform