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

SQL SERVER - Difference Between ORIGINAL_LOGIN() and SUSER_SNAME() ExecuteAsLogin

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)

,
Previous Post
SQL SERVER – Query to Get Unique Distinct Data Based on Condition – Eliminate Duplicate Data from Resultset
Next Post
SQL SERVER – Services Pack 2 for SQL Server 2008 R2 – Microsoft SQL Server 2008 R2 Service Pack 2

Related Posts

2 Comments. Leave new

  • David Hernandez
    January 25, 2013 1:32 am

    Hi, I’m trying to create an sp with Execute as … but an error mesage appears saying that the user doesn’t have permision to do that. I’m not sure what to configurate in user properties. The main thing is about I want to read an Excel Linked server without beeing in sysadmin role, that’s why i’m trying to run a query with execute as someone who is in that role.

    For any help, thanks in advanced

    Reply
  • Hi Pinal, I am trying to get system user login. I am logged in to sql server with a different service account.. Is it possible?

    Reply

Leave a Reply

Menu