SQL Interview: 65 Auditing user actions in T-SQL

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Security Level: Medium

Question:

You have code in stored procedures that records which users have called the procedures. The stored procedures are defined as:

WITH EXECUTE AS OWNER

You find that when you log the caller of the procedure, you get the owner of the procedure, rather than the caller.

How can you get the correct caller, regardless of impersonation levels?

Answer:

The ORIGINAL_LOGIN() function provides details from the bottom of the impersonation stack, regardless of how many levels of impersonation have happened.

2025-07-23