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 (http://blog.sqlauthority.com)

2 thoughts on “SQL SERVER – Difference Between ORIGINAL_LOGIN() and SUSER_SNAME()

  1. 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

    Like

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #040 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s