SQL: Make sure to use ORIGINAL_LOGIN when auditing

I regularly see code where SQL Server DBAs and developers are trying to log which user/login took a particular action within the database. There are many functions which appear to return the information needed, but there's only one that should normally be used: ORIGINAL_LOGIN().

A login is the way that a connection is authenticated to the server ie: it's the "who are you?" at the server level.

Most times, a user is a mapping of that login to a particular database.  The login and user will often have the same name (and I'd recommend that you do that to avoid confusion) but they do not have to be. A login Terry could be a user Mary in one database and a user Nga in another database.

A user can also represent the role that a login plays in the database. This is commonly see with administrators who will appear as dbo (ie: database owner) within each database, rather than their actual names.

So, using a user name within a database for auditing is normally not appropriate. 

A further complication is that a security context can be changed by impersonation. For example, a user might execute a procedure that has a WITH EXECUTE AS clause. Functions like USER_NAME() will return the impersonated context, not the actual context.

What you normally really want is the actual person who has logged in. ORIGINAL_LOGIN() goes to the bottom of the security stack, and returns that value. It's typically what I use for any type of auditing.

You can see its action in the main image above. Note what happens in the second (impersonated) context.

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *