Move the DROP TRIGGER so that it is executed prior to CREATE of a new trigger, to avoid granting excessive permissions to end users

[CashmoreP]

Rather than having a DROP TRIGGER inside the CREATE TRIGGER code, could the drop be performed prior to the CREATE TRIGGER, e.g.

get a list of all triggers from sys.triggers matching tr_{schema}{tableName}*_Sender
drop each trigger matching this
then create the new trigger

The current approach means that security has to be changed to grant DROP TRIGGER permission to any user who may update the table. The alternative approach means that DROP TRIGGER permission can be restricted to the user who would already have CREATE TRIGGER permissions.

[christiandelbianco]

Hi CashmoreP ,
can you explain me better your idea. I do not understand...Thanks

[CashmoreP]

Consider the following scenario:

User 1 has permission to create and drop triggers
User 2 has no permission to create and drop triggers

If user 2 attempts to update data in a table that has a tr_{schema}{tableName}*_Sender trigger on it, the trigger SQL code could result in an attempt to invoke a DROP TRIGGER command, which fails because user 2 has not got sufficient permissions.

The solution is to remove the DROP TRIGGER call from the tr_{schema}{tableName}*_Sender trigger and drop the trigger in a different area of code. I can get one of my colleagues to commit changes so that you can see the end result.

[christiandelbianco]

@CashmoreP Yes, you are perfectly right. Good suggestion!!!
As you probably already have fixed it, can you tell me the point where you think is better to move the code that drop the trigger?

    DECLARE @conversationHandlerExists INT
    SELECT @conversationHandlerExists = COUNT(*) FROM sys.conversation_endpoints WHERE conversation_handle = '3259dc30-8931-ea11-a5bc-9cb6d0c4de6c';
    IF @conversationHandlerExists = 0
    BEGIN
        DROP TRIGGER [tr_dbo_Products_950f74d5-2f11-493a-a325-d992d1040cb3_Sender];
        RETURN
    END

[CashmoreP]

Christian I believe that one of my colleagues, Ayomide Kehinde, has already pushed code changes up on a separate branch. Let me know if you can see this change. If not, I will get Ayo to contact you

On Tue, 7 Jan 2020 at 20:12, Christian Del Bianco ***@***.***> wrote: @CashmoreP <https://github.com/CashmoreP> Yes, you are perfectly right. Good suggestion!!! As you probably already have fixed it, can you tell me the point where you think is better to move the code that drop the trigger? DECLARE @conversationHandlerExists INT SELECT @conversationHandlerExists = COUNT(*) FROM sys.conversation_endpoints WHERE conversation_handle = '3259dc30-8931-ea11-a5bc-9cb6d0c4de6c'; IF @conversationHandlerExists = 0 BEGIN DROP TRIGGER [tr_dbo_Products_950f74d5-2f11-493a-a325-d992d1040cb3_Sender]; RETURN END — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#170>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA265FYS62D6IQWWWRTK2FTQ4TO4TANCNFSM4J3LPVHQ> .

-- Regards, Paul (sent from Gmail mobile)

[christiandelbianco]

Thanks. As usual, to anyone that find a bug and also propose a suggestion, I ask if can i put his/their name(s) in contributors page. So, if for you is ok, just write me some words (if you want) about your profile as done by the other developers.

[CashmoreP]

Christian Yes, will do tomorrow Regards Paul

On Tue, 7 Jan 2020 at 20:22, Christian Del Bianco ***@***.***> wrote: Thanks. As usual, to anyone that find a bug and also propose a suggestion, I ask if can i put his/their name(s) in contributors page <https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency/wiki/Contributors>. So, if for you is ok, just write me some words (if you want) about your profile as done by the other developers. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#170>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA265FYJMY2YNUPN63XF2ZTQ4TQAFANCNFSM4J3LPVHQ> .

-- Regards, Paul (sent from Gmail mobile)

[CashmoreP]

Christian Here is the link to the commits that Ayomide made, containing the changes: https://github.com/kkehinde/monitor-table-change-with-sqltabledependency/commits?author=kkehinde For the contributors page, you can just put that I am Paul Cashmore, Head of Development and Slater and Gordon Group (a law firm). Regards, Paul

…

On Tue, 7 Jan 2020 at 20:22, Christian Del Bianco ***@***.***> wrote: Thanks. As usual, to anyone that find a bug and also propose a suggestion, I ask if can i put his/their name(s) in contributors page <https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency/wiki/Contributors>. So, if for you is ok, just write me some words (if you want) about your profile as done by the other developers. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#170>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA265FYJMY2YNUPN63XF2ZTQ4TQAFANCNFSM4J3LPVHQ> .

[christiandelbianco]

Added clause EXECUTE AS SELF to trigger, in order to run it in the context of the creator.

[christiandelbianco]

Fix available in release 8.5.7