Earlier this week I was scripting out a table with some legacy columns (InsertDate and InsertBy for example). If you have created these type of columns before you have probably put a default on each. InsertDate is pretty easy, probably something like GETDATE() or GETUTCDATE(). But what do you put for InsertBy?

TL;DR: ORIGINAL_LOGIN is the only function that consistently returns the actual login name that we started with regardless of impersonation.

I’ve known for awhile that there are several functions you can use but it wasn’t until I started looking carefully that I noticed just how many there truly are. And they are different in several ways. Note: I’m not going to assume this is all of them but it is all that I’ve been able to find.

In order to really understand the results of each function I’m going to do, you guessed it, some testing. To start with I’m going to create a pair of logins and associated users. The users are going to have a different name from the logins so we can tell them apart. And I’m creating two login/user pairs so that I can test impersonation. And lastly, you will probably notice I’m granting some high level permissions for these principals. I’m running these tests on a local instance, I’m going to remove them when I’m done and I don’t want to mess with security for this particular set of tests. (And there is another reason but you’ll see it later.)

First log into the instance as MyLogin then run the following script. This creates a baseline table with values for each function. But even more than that it will let us know what the datatypes are for each.

-- Run as MyLogin/MyUser
USE Test;
GO
SELECT
'Baseline ' AS TestName ,
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
CURRENT_USER AS [CURRENT_USER],
SESSION_USER AS [SESSION_USER],
SYSTEM_USER AS [SYSTEM_USER],
USER_NAME() AS [USER_NAME],
USER AS [USER],
SUSER_SNAME() AS [SUSER_SNAME],
SUSER_NAME() AS [SUSER_NAME]
INTO UserTest;

Now we do a Baseline2 using the same login/user but this time taking away sysadmin from MyLogin and just giving MyUser read/write and execute permissions.

First of all of them are nvarchar(128) except ORIGINAL_LOGIN which was nvarchar(4000). If you look in BOL you will note that some of these say they return sysname and others a specific character length. I’ve also found refereces that sysname is equivilent to nvarchar(128) but regardless ORIGINAL_LOGIN still consistently gives me an nvarchar(4000). As for the actual values returned:

To start with ORIGINAL_LOGIN, SYSTEM_USER, SUSER_SNAME and SUSER_NAME all returned server principals and CURRENT_USER, SESSION_USER, USER_NAME, and USER return database principals. Using either of the EXECUTE AS cases all but ORIGINAL_LOGIN returned the impersonated login/user. And with the other impersonation option (the SP using EXECUTE AS OWNER) all but ORIGINAL_LOGIN returned dbo and sa (the database owner FYI). Leaving our two Baseline tests for last. Both returned MyLogin for the login functions but the sysadmin returned dbo and regular permissions returned MyUser. I did a test on my own putting MyUser into the db_owner role and it still returned MyUser, which is why I ran a test with sysadmin. See I said there was a reason.

So to make a long story short the only function that returned the same thing across the board is ORIGINAL_LOGIN. So even though it is quite a bit larger this is without a doubt my choice for any legacy columns.

And last but not least here is the promised cleanup of the users and logins.