Pages

Monday, 18 July 2011

Who Am I?

SQL Server has no less than 13 functions to capture the current "person" running a piece of code. (And I may have missed some too!)
  1. USER
  2. CURRENT_USER
  3. SESSION_USER
  4. SYSTEM_USER
  5. USER_NAME()
  6. USER_ID()
  7. USER_SID()
  8. SUSER_NAME()
  9. SUSER_SNAME()
  10. SUSER_ID()
  11. SUSER_SID()
  12. ORIGINAL_LOGIN()
  13. DATABASE_PRINCIPAL_ID()
Why so many? What do they all do? Why do some end in parentheses and some do not? And, more importantly, which one do I really need?


For something as simple as finding out who is calling a piece of T-SQL, there sure do seem to be a lot of functions!  Lets take a look at what each ones does; paraphrasing from Books OnLine...

USER

The USER function returns the name of the currently executing context.  If the context is switched with an EXECUTE AS the impersonated context is used.

This function returns the current database user.

Return type: CHAR

CURRENT_USER

CURRENT_USER returns the name of the current security context.  If the context is switched with an EXECUTE AS the impersonated context is used. This function returns the current database user.

Return type: SYSNAME

SESSION_USER

SESSION_USER returns the name of the current security context.  If the context is switched with an EXECUTE AS the impersonated context is used. This function returns the current database user.

Return type: NVARCHAR(128)

SYSTEM_USER

SYSTEM_USER returns the name of the current security context.  If the context is switched with an EXECUTE AS the impersonated context is used. This function returns the current login, not the database user.

Return type: NCHAR

USER_NAME()

The USER_NAME() function is principally the same as the USER function.  It can optionally take a database_principal_id as an input value and will return the database user of that ID if found.

Return type: NVARCHAR(256)

USER_ID()

This returns the identification number of the database user.  What this means is that it returns the database_principal_id from sys.database_principals.  Or if you're stuck on SQL 2000 uid from sysusers.  The function can optionally take a database user as input and return the ID for that user if found.  If the function is called with the EXECUTE AS statement and no input parameters it returns the ID of the impersonated account.

Return type: INT

Note:  This function is to be deprecated.  It is replaced by the DATABASE_PRINCIPAL_ID() function.

USER_SID()

This function seems to be strangely absent from Books Online.  It returns the security identifier (SID) of current security context.  It can optionally take a user id (database_principal_id) value as input.  If the context is switched with an EXECUTE AS the impersonated context is used.

Return type: VARBINARY(85)

SUSER_NAME()

Returns the login name associated with a database user which can optionally be passed into the function. When called without parameters it takes the current security context.  If the context is switched with an EXECUTE AS the impersonated context is used.

Return type: NVARCHAR(128)

SUSER_SNAME()

Returns the login name associated with a security identification number (SID) which can optionally be passed into the function. When called without parameters it takes the current security context.  If the context is switched with an EXECUTE AS the impersonated context is used.

Return type: NVARCHAR(128)

SUSER_ID()

Returns the login identification number of the user.  In SQL 2000 this always returned null.  Starting with SQL 2005 this returned the principal_id in sys.server_principals.  This function can optionally take the name of a login as input.  If the security context is switched with an EXECUTE AS the impersonated context is used.  Only if a login is explicitly defined is a valid value returned.  Therefore any login that gains access through group inheritance returns the "public" server role sid.

Return type: INT

SUSER_SID()

Returns the security identification number (SID) for the specified login.  When the login is not explicitly passed in the current security context is used. If the security context is switched with an EXECUTE AS the impersonated context is used.

Return type: VARBINARY(85)

ORIGINAL_LOGIN()

Returns the name of the login that connected to the instance of SQL Server.  If the security context is switched with an EXECUTE AS statement the original context is used.

Return type: SYSNAME

DATABASE_PRINCIPAL_ID()

Returns the ID number of a principal of the current database.  It can optionally take the name of a database principal as input.  If the principal name is omitted the current database principal is used. If the security context is switched with an EXECUTE AS the impersonated context is used.

Return type: INT

Examples

Thats a lot of information to try and digest. Many of the functions would appear to be identical from their descriptions.  Its therefore best to show the subtleties of each function with a few examples.

Throughout these examples, I'll be running the following T-SQL to demonstrate each of the functions in various scenarios:
select [Function] = 'user' ,Result = convert(sql_variant, user) union all
select [Function] = 'current_user' ,Result = convert(sql_variant, current_user) union all
select [Function] = 'sesion_user' ,Result = convert(sql_variant, session_user) union all
select [Function] = 'user_name()' ,Result = convert(sql_variant, user_name()) union all
select [Function] = 'user_id()' ,Result = convert(sql_variant, user_id()) union all 
select [Function] = 'user_sid()' ,Result = convert(sql_variant, user_sid()) union all 
select [Function] = 'system_user' ,Result = convert(sql_variant, system_user) union all 
select [Function] = 'suser_name()' ,Result = convert(sql_variant, suser_name()) union all 
select [Function] = 'suser_sname()' ,Result = convert(sql_variant, suser_sname()) union all 
select [Function] = 'suser_id()' ,Result = convert(sql_variant, suser_id()) union all 
select [Function] = 'suser_sid()' ,Result = convert(sql_variant, suser_sid()) union all 
select [Function] = 'original_login()' ,Result = convert(sql_variant, original_login()) union all
select [Function] = 'database_principal_id()', Result = convert(sql_variant, database_principal_id())

Note: I wouldn't normally advocate the use of the SQL_VARIANT datatype, but in this case its quite useful to concatenate the results into a single output.

To help understand the screen shots here are some notes about the setup/configuration of the SQL Instance.

Login TypeLogin NameDatabase User
WindowsBARGHEST\WhoAmIWhoAmI_win
SQLWhoAmI_stdWhoAmI_db

Changing login impersonationEXECUTE AS LOGIN = 'WhoAmI_std';
Changing DB impersonationEXECUTE AS USER = 'WhoAmI_db';

Windows login no impersonation

In this scenario we connect to the instance of SQL Server as a windows authenticated account and run the script.
As you would expect, we see the Windows login used (BARGHEST\WhoAmI) and the associated database user (WhoAmI_win)

Standard login no impersonation

In this scenario we connect to the instance of SQL Server as a SQL authenticated account and run the script.
As you would expect, we see the SQL login used (WhoAmI_std) and the associated database user (WhoAmI_db)

Windows login impersonating the WhoAmI_std login

In this scenario we connect to the instance of SQL Server as a windows authenticated account, change the context to the WhoAmI_std login and run the script.

Of note here is the fact that all functions return the impersonated login and user with the exception of the ORIGINAL_LOGIN() function.

Windows login impersonating the WhoAmI_db user

In this scenario we connect to the instance of SQL Server as a windows authenticated account, change the context to the WhoAmI_db user and run the script.

The expected database user (WhoAmI_db) is returned by the functions, what may be of surprise is that the login (WhoAmI_std) associated with the impersonated user is also returned.

Standard login impersonating a Windows login

In this scenario we connect to the instance of SQL Server as a SQL authenticated account, change the context to the BARGHEST\WhoAmI login and run the script.

Standard login impersonating the db user WhoAmI_db

In this scenario we connect to the instance of SQL Server as a SQL authenticated account, change the context to the WhoAmI_db user and run the script.

Summary

Each function has its own unique facet that make them each useful in their own right. There's a lot to take in with all these functions and I'm danger of leaving you more confused than when we started.

I haven't even explored using the EXECUTE AS functionality in procedures and will save that for another article as I've presented more than enough here!

The general rule of thumb I follow for remembering the function, and I'm aware its not perfect, is this:
If the function ends ID it returns the database principal
It the function ends SID it returns the server principal
If the function ends USER it returns the database user name
If the function ends NAME it returns the server login name
And like learning a foreign language you just have to remember the exceptions (CURRENT_USER and SESSION_USER anyone?)

I recommend you try out each of the functions yourself, though I think you'll find you settle on one or two you use regularly. For example; traditionally I had used SUSER_SNAME() but now use ORIGINAL_LOGIN() to identify who is making the database call. I'm usually only interested in who the person sat at the keyboard is and therefore ORIGINAL_LOGIN() is the best choice for this.

No comments:

Post a Comment