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.

ORIGINAL_LOGIN()
CURRENT_USER
SESSION_USER
SYSTEM_USER
USER_NAME(database user id)
USER
SUSER_SNAME(sid)
SUSER_NAME(server user id)

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.)

-- Run with a user with advanced permissions.
USE master;
GO
CREATE LOGIN MyLogin WITH PASSWORD = 'MyLogin', CHECK_POLICY = OFF;
EXEC sp_addsrvrolemember 'MyLogin','sysadmin'
CREATE LOGIN MyImpLogin WITH Password = 'MyImpLogin',
CHECK_POLICY = OFF;
GO
USE Test;
GO
CREATE USER MyUser FROM LOGIN MyLogin;
CREATE USER MyImpUser FROM LOGIN MyImpLogin;
EXEC sp_addrolemember 'db_owner','MyImpUser';
GO

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 ,
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.

-- Run with a user with advanced permissions.
USE master;
EXEC sp_dropsrvrolemember 'MyLogin','sysadmin';
GRANT IMPERSONATE ANY LOGIN TO MyLogin;
GO
USE Test;
GO
EXEC sp_addrolemember 'db_datareader','MyUser';
EXEC sp_addrolemember 'db_datawriter','MyUser';
GRANT EXECUTE TO MyUser;

-- Run as MyLogin/MyUser
USE Test;
GO
INSERT INTO UserTest VALUES (
'Baseline2',
ORIGINAL_LOGIN(),
CURRENT_USER,
SESSION_USER,
SYSTEM_USER,
USER_NAME(),
USER,
SUSER_SNAME(),
SUSER_NAME());

Next we are going to use EXECUTE AS to change our current context to MyImpLogin (EXECUTE AS LOGIN) then MyImpUser (EXECUTE AS USER).

-- Run as MyLogin/MyUser
USE Test;
GO
EXECUTE AS Login = 'MyImpLogin';
INSERT INTO UserTest VALUES (
'Impersonate Login',
ORIGINAL_LOGIN(),
CURRENT_USER,
SESSION_USER,
SYSTEM_USER,
USER_NAME(),
USER,
SUSER_SNAME(),
SUSER_NAME());
REVERT;
EXECUTE AS User = 'MyImpUser';
INSERT INTO UserTest VALUES (
'Impersonate User',
ORIGINAL_LOGIN(),
CURRENT_USER,
SESSION_USER,
SYSTEM_USER,
USER_NAME(),
USER,
SUSER_SNAME(),
SUSER_NAME());
REVERT;

And for a final test I want to use the EXECUTE AS OWNER clause of a stored procedure.

-- Run with a user with advanced permissions.
USE Test
GO
CREATE PROCEDURE usp_ImpTest
WITH EXECUTE AS OWNER
AS
INSERT INTO UserTest VALUES (
'Impersonate SP',
ORIGINAL_LOGIN(),
CURRENT_USER,
SESSION_USER,
SYSTEM_USER,
USER_NAME(),
USER,
SUSER_SNAME(),
SUSER_NAME());
GO

-- Run as MyLogin/MyUser
USE Test;
GO
EXEC usp_ImpTest;
GO

And here is the promised cleanup of the users and logins.

-- Run with a user with advanced permissions.
USE master;
DROP LOGIN MyLogin;
DROP LOGIN MyImpLogin;
USE Test;
DROP USER MyUser;
DROP USER MyImpUser;

So what were the results?

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.

-- Run with a user with advanced permissions.
USE master;
DROP LOGIN MyLogin;
DROP LOGIN MyImpLogin;
USE Test;
DROP USER MyUser;
DROP USER MyImpUser;
DROP TABLE UserTest;
DROP PROCEDURE usp_ImpTest;

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: microsoft sql server, system functions, T-SQL, user functions