Sql Server Identity and Membership Functions

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'

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.