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 DetailsSELECT 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 userEXECUTE AS LOGIN = 'TestLogin'GOSELECT ORIGINAL_LOGIN() OriginalLogin, SUSER_SNAME() CurrentLogin
GO-- Revert Execution as another userREVERT
GO-- Get Login DetailsSELECT ORIGINAL_LOGIN() OriginalLogin, SUSER_SNAME() CurrentLogin
GO-- Clean upDROP 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.
No comments:
Post a Comment