Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Rights needed to drop a trigger and restore it.
Message
General information
Forum:
Microsoft SQL Server
Category:
Security
Miscellaneous
Thread ID:
01200883
Message ID:
01200897
Views:
19
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--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform