ORIGINAL_LOGIN() – How to identify the Original Login Name

Introduction

A few days ago I showed you the two functions SUSER_NAME() and SUSER_SNAME(). Both tell us the name of the login that was used to execute the query. Today I want to introduce you to a third one, ORIGINAL_LOGIN(), that does that same thing. Or does it?

ORIGINAL_LOGIN() Example

Books Online says about the ORIGINAL_LOGIN() function: "You can use this function to return the identity of the original login in sessions in which there are many explicit or implicit context switches."

Let's look at an example to see what that means. To compare ORIGINAL_LOGIN() with SUSER_SNAME() we are going to use the simple select statement:

After connecting to the SQL Server instance with the login TestLogin1 both functions return the same value:

So, let's see what happens after a security context switch. Remember, to switch the security contest of a connection, we can use the EXECUTE AS statement.

After switching the security context to TestLogin2, SUSER_SNAME() returns the new login's name whereas ORIGINAL_LOGIN() still returns TestLogin1:

As you can see, ORIGINAL_LOGIN() tells us who made the connection to SQL Server even after an EXECUTE AS was executed. This works even if multiple nested context switches happened on the connection:

This really demonstrates the difference between SUSER_SNAME() and ORIGINAL_LOGIN() nicely. While the former is affected by context switched and always returns the name of the currently active server principal, the latter is not and always returns the same value, the name of the login that initiated the connection.

Summary

If you need to know the name of the original login that initiated the connection to the SQL Server instance, you can use the ORIGINAL_LOGIN() function. It always returns the name of the original login, independent of how many security context switches for example with EXECUTE AS have been executed.