Post reply

Can trigger execution be excluded only for a particular login?

  • October 28, 2023 at 9:50 am

    #4316519

    Hi,

    I have an ERP application which connects to SQL 2019 database by an user - "vwuser". Whenever the users does transactions via ERP , data population happens. Very few people have backend access, but auditors are asking for tracking the data modifications that happen via Backend (say data corrections via Update / Delete).

    The ERP already has triggers inbuilt for tracking changes, which stores the previous and modified data in another database. Trying to exclude transaction done via ERP as it is not possible to modify data once transaction is complete and separate log is already available for the modification cases.

    Is there any server settings that can be done to prevent the execution of trigger for vwuser ? (or) is adding system_user = 'VWUSER'  return  in all the triggers the only option?

  • October 28, 2023 at 6:41 pm

    #4316698

    I believe the latter that you mention is the only way.

    I also want to warn than none of what has done (according to what you've described) will hold up in a court of law, if it ever comes to that, because anyone with the appropriate privs could go in and make undetectable changes.  IMHO, it's not even substantial enough to pass a required audit.  Unfortunately, the methods are are capable of such scrutiny will record everything.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 29, 2023 at 6:14 am

    This was removed by the editor as SPAM

  • November 2, 2023 at 6:53 am

    #4319475

    I totally agree with all aspects of Jeff's response.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply