On my current research project I’m looking at security. The number of different functions available (some of which are obsolescent) has always been an irritation, so I decided to create a Transact-Sql query that demonstrates the functions and provides links to the documentation. You can use EXECUTE AS LOGIN or EXECUTE AS USER to explore what’s returned for other security principals, always assuming you have the necessary permissions of course. Hope you find it useful.

SELECT 'SUSER_SNAME(SUSER_SID())' As FunctionCall, SUSER_SNAME(SUSER_SID()) As Result, 'https://msdn.microsoft.com/en-us/library/ms174427.aspx' As Reference
UNION ALL
SELECT 'SUSER_NAME()', SUSER_NAME(), 'https://msdn.microsoft.com/en-us/library/ms187934.aspx'
UNION ALL
SELECT 'USER_NAME()', USER_NAME(), 'https://msdn.microsoft.com/en-us/library/ms188014.aspx'
UNION ALL
--equivalent to USER_NAME()--
SELECT 'CURRENT_USER', CURRENT_USER, 'https://msdn.microsoft.com/en-us/library/ms176050.aspx'
UNION ALL
SELECT 'SESSION_USER', SESSION_USER, 'https://msdn.microsoft.com/en-us/library/ms177587.aspx'
UNION ALL
SELECT 'SYSTEM_USER', SYSTEM_USER, 'https://msdn.microsoft.com/en-us/library/ms179930.aspx'
UNION ALL
SELECT 'IS_MEMBER(''public'')', CAST(IS_MEMBER('public') as nvarchar(10)), 'https://msdn.microsoft.com/en-us/library/ms186271.aspx'
UNION ALL
SELECT 'IS_ROLEMEMBER(''db_datareader'')', CAST(IS_ROLEMEMBER('db_datareader') as nvarchar(10)), 'https://msdn.microsoft.com/en-us/library/ee677633.aspx'
UNION ALL
SELECT 'IS_SRVROLEMEMBER(''bulkadmin'')', CAST(IS_SRVROLEMEMBER('bulkadmin') as nvarchar(10)), 'https://technet.microsoft.com/en-us/library/ms176015(v=sql.120).aspx'
UNION ALL
SELECT 'ORIGINAL_LOGIN()', ORIGINAL_LOGIN(), 'https://msdn.microsoft.com/en-us/library/ms189492.aspx'