SQL SERVER – Difference Between ORIGINAL_LOGIN() and SUSER_SNAME()

Today let us start today’s blog post with a simple start question which I was asked by reader of my latest book SQL Server Interview Questions and Answers. Indeed a good question warrants a good answer with a script associated with the same.

Question: What is the difference between ORIGINAL_LOGIN() and SUSER_SNAME() and when will I use it?

Function ORIGINAL_LOGIN() returns the name of the original or very first login that connected to the instance of SQL Server and it is used to identity of the original login in sessions. If there is an application or database where context switching is happening quite often this can be a very useful feature for auditing. Let us see a quick T-SQL example where we initially retrieve current user and original login together you will notice that they are same. Afterwards we will create a new user and we will set the context of the execution to the new user. Later we will run the same command and you will notice that the current user name has been changed but the original login remains the same.

USE AdventureWorks2012;
GO
-- Get Login Details
SELECT ORIGINAL_LOGIN() OriginalLogin, SUSER_SNAME() CurrentLogin
GO
--Create a temporary login and user.
CREATE LOGIN TestLogin WITH PASSWORD = 'Complex@1';
CREATE USER TestUser FOR LOGIN TestLogin;
GO
-- Execute as another user
EXECUTE AS LOGIN = 'TestLogin'
GO
SELECT ORIGINAL_LOGIN() OriginalLogin, SUSER_SNAME() CurrentLogin
GO
-- Revert Execution as another user
REVERT
GO
-- Get Login Details
SELECT ORIGINAL_LOGIN() OriginalLogin, SUSER_SNAME() CurrentLogin
GO
-- Clean up
DROP LOGIN TestLogin;
DROP USER TestUser;
GO

This feature can be very useful in the auditing as well figuring out who is the original user to the session. Here I have a quick question – are you using this feature in your application? What is the real use case scenario for the same.

Reference: Pinal Dave (https://blog.sqlauthority.com)