Disabling Triggers on Replicated Tables (Execute As)

I was recently given the requirement to allow for a user process to disable the triggers on a table. This can be done easily by granting the user Alter rights on the table in SQL 2005/2008, but this table is replicated and only owners or sysadmins can disable the triggers on replicated tables.

Below is the error message the user received when trying to disable the triggers on the replicated table:

Msg 21050, Level 14, State 1, Procedure sp_MSreplcheck_publish, Line 16

Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

The “solution”, and I put it in quotes because I’m not real fond of it, is to grant this user the right to impersonate an elevated account, in this case, DBO.

NOTE: You should be very careful when granting impersonation and understand the security consequences when granting those rights. I would always abstract any code that is doing impersonation with a Stored Procedure.

First, this must be ran on the database where the user is needing to disable the triggers

Grant IMPERSONATE ON User:: dbo to [<user>]

Then, inside a Stored Procedure, the following code would be executed:

Execute AS USER =  'dbo'

Alter Table <Table> Disable Trigger <triggername>|| ALL

Revert

……..Code

Execute AS USER =  'dbo'

Alter Table <Table> Disable Trigger <triggername>|| ALL

Revert

The REVERT keyword is very important in this case because after the trigger has been dropped, we want the user to REVERT back to using their own security.