skip to main content

ORIGINAL_LOGIN vs SYSTEM_USER for audit purposes in SQL Server

I am implementing an inserted trigger and am considering using ORIGINAL_LOGIN function to capture the current executing user.

I was reading up on this and came across SYSTEM_USER.

Which one do I use?

I asked on StackOverflow and got this very helpful response:

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.

More reading material:
http://www.sommarskog.se/grantperm.html