Thursday, October 22, 2009

USER_NAME(), SUSER_NAME(), ORIGINAL_LOGIN()

Sometimes, we switch the execution context to different account when required. One of requirements when connected in such a manner may be, finding out the original account. This can be retrieved from ORIGINAL_LOGIN function. Not only this, the other functions such as USER_NAME, SUSER_NAME are useful too, if you need to info return from them. Thought to put down a small code, just to show the different between these functions; Login as "sa" and execute... -- Create a login and a user USE master GO CREATE LOGIN TestLogin WITH PASSWORD = '123', CHECK_POLICY = OFF GO USE AdventureWorks GO CREATE USER TestUser FROM LOGIN TestLogin -- Test the functions -- Returns dbo SELECT USER_NAME() -- Returns sa SELECT SUSER_NAME() -- Switch the execution context EXECUTE AS LOGIN = 'TestLogin' -- Returns TestUser SELECT USER_NAME() -- Returns TestLogin SELECT SUSER_NAME() -- Returns sa SELECT ORIGINAL_LOGIN() REVERT; USER_NAME: Returns the current user in the current context. If the user_id is submitted, returns the name of the given id. SUSER_NAME: Returns the current login in the current context. If server_user_id is submitted, returns the name of given id. ORIGINAL_LOGIN: Returns the original login in the session in which there are many implicit or explicit context switches.

No comments: