Search code examples
sql-servertriggersauditing

ORIGINAL_LOGIN and SYSTEM_USER functions for auditing?

I am implementing an inserted trigger and am considering using ORIGINAL_LOGIN function to capture the current executing user. I would like to know the pros and cons of using ORIGINAL_LOGIN for auditing. Under what scenario does SYSTEM_USER provide a better alternative to ORIGINAL_LOGIN?

Solution

  • SYSTEM_USER presents you with the credentials used to run the query. This is important to establish which permissions were active. ORIGINAL_LOGIN is giving you the user with which the connection was established. This is also important information.

    SYSTEM_USER

    • Pro: you can see with which permissions a query was executed.
    • Con: you don't know who originally created the connection

    ORIGINAL_LOGIN

    • Pro: You see who created the connection.
    • Con: You don't know with which permissions the query was executed.

    To have correct audit results, both need to be logged.