ORIGINAL_LOGIN vs SYSTEM_USER for auditing
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 connectionORIGINAL_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