none
DISABLE/ENABLE TRIGGER permissions RRS feed

  • Question

  • I have an AFTER INSERT trigger on a table that updates the CreatedBy column with the current user's login name -- SUSER_SNAME(). The first statement in the trigger disables the UPDATE trigger so I can update CreatedBy without it firing:

    DISABLE TRIGGER tr_Supplier_UPDATE ON QE.dbo.Supplier

    The users only have DataReader/Writer permissions so when they try adding a new record it throws this error...

    Msg 1088, Level 16, State 120, Procedure tr_Supplier_INSERT, Line 16 [Batch Start Line 48]
    Cannot find the object "tr_Supplier_UPDATE" because it does not exist or you do not have permissions.

    So I added WITH EXECUTE AS OWNER...

    CREATE TRIGGER dbo.tr_Supplier_INSERT ON QE.dbo.Supplier 
    	WITH EXECUTE AS OWNER											
    	AFTER INSERT

    The good news is that now it doesn't throw the error. The bad news is that instead of SUSER_SNAME() giving me the current user's login, it gives me the login of some DBA in the MASTER database!

    My question then is: how do I permit the DISABLE TRIGGER and still get the real current user name?

    Thanx!


    Darrell H Burns

    Tuesday, September 17, 2019 10:18 PM

All replies

  • Use ORIGINAL_LOGIN() instead of SUSER_SNAME()?

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, September 18, 2019 6:51 AM
    Moderator
  • That worked. Thanx!

    Darrell H Burns


    Wednesday, September 18, 2019 3:58 PM
  • If CreatedBy is only updated by your trigger, a better thing than disabling the trigger is to add code to your tr_Supplier_UPDATE to not run if the only update is to CreatedBy.

    IF UPDATE(CreatedBy)
    RETURN;
    

    You could also use COLUMNS_UPDATED but that is a little more complicated.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/columns-updated-transact-sql?view=sql-server-2017

    Wednesday, September 18, 2019 5:45 PM
    Moderator
  • To answer your question as such: you sign the trigger with a certificate, create a user from the certificate, and grant that user the permissions needed.

    But it is the wrong solution anyway. Tom's suggestion is a lot better. An alternative, if the situation is more complex is use a global flag. In the past I used temp tables for this. That is, I have code that goes:

    IF object_id('tempdb..#myflag') IS NOT NULL
       RETURN

    Starting with SQL 2016 you can use session context which is cleaner.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, September 18, 2019 9:37 PM