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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment