SYSTEM_USER and it’s limitations

Posted On: In: Microsoft SQL Server

Recently I came across a stored procedure I had written a few years ago, when I first started as a developer I was given a task where I needed to write a stored procedure that allowed users to update an underlying table but also track the changes, what data had been amended and who amended it, to make it work I made use of an audit table to capture them details around which users were doing what. To achieve this I had selected to use SYSTEM_USER not knowing at the time it’s limitations.

I ran into a scenario a little while ago where the underlying data had been updated but the audit table suggested that a user had updated the data who said they didn’t, SYSTEM_USER was raised as a potential problem and not knowing it could record another user’s context I set about investigating how this works.

So like all things, I built out a test to see how this could have potentially happened. First thing is first, I need a stored procedure that I could use, my instance of SQL has a database called DBA_Tasks in here I put a stored procedure called LoginTest

What this will do is return me the following;

  1. The executing user
  2. The user who is logged into the computer
  3. The username without the computer name

Okay so let’s find out what we get back from that stored procedure if I just execute it as me

I get exactly what I am expecting, my username is in both the SYSTEM_USER and ORIGINAL_LOGIN() function returns.

Now I need another user in that database to test against, someone that isn’t me. I am going to map this user to the DBA_Tasks database and give them db_owner permissions

Now that we have another user, let’s try executing that stored procedure as them;

I am still logged into the computer as me but what results am I going to get back from my test stored procedure?

As you can see, SYSTEM_USER has returned the principal that we ran the stored procedure as, if we are trying to capture WHO ran that stored procedure this obviously wouldn’t be sufficient, ORIGINAL_LOGIN() however has given us the username of the principal logged into the machine and where the query was being executed which in this case would have been correct.

Reading through the documentation for SYSTEM_USER we see the following;

Of course we had given the login db_owner to the DBATasks database in the first test, so they could essentially do anything that they pleased within that database, but what if the user has just select permissions against the database?

Let’s try executing the stored procedure now

As you can see from the result returned, we are not allowed to do that

Even if we give the user db_datawriter permissions we still can’t impersonate another user, the principal needs explicit impersonate permissions, db_owner or be a sysadmin for this to work.

Wanna see? Ok let’s demonstrate that;

Our user still only has datawriter, datareader and execute permissions to the DBATasks database but let’s try executing our stored procedure as DBATasksTest now

There we have it, we are now executing that stored procedure as someone else.

We can check if a login has IMPERSONATE permissions by running the following TSQL query against the instance, 1 or 0 will be returned depending on the permission set.

It is clear that SYSTEM_USER wouldn’t be suitable for the use case we are proposing, current user will return the windows principal even when EXECUTE AS has been executed before the stored procedure was called, of course as shown in our tests if the individual that is running the stored procedure has full access to the underlying table none of this really matters as they could simply run an update to rid their name from existence from that table anyway.