Using SYSTEM_USER to Filter Data

In this post I want to show a cool trick I discovered recently that has made my life much easier. I build a lot of database systems with SQL server holding the data and a variety of technologies used for the front-end interface, mainly MS Access (unfortunately!). Sometimes I have a need to filter the data based on who is viewing it, and using SYSTEM_USER has made this much easier with just a little bit of prep work. The system I'm building right now will be used to distribute surveys to our call centers so their employees can call the appropriate people with this survey.

Before getting into the details, let's see what SYSTEM_USER actually does:

The above screenshot shows the domain (one of my development VMs) followed by a slash ( \ ) and the username I log in with. Because we use Windows Authentication with Active Directory, I assume this is fairly hard to bypass but I do not know that for certain. Regardless, it's much more secure than putting the similar code inside VBA in Access where anyone with a clue can fiddle with it. Alright... now to the real work...

Creating Structure

First, we need an employees table to store the data on the employees who will be accessing the system. Below is code to create a portion of that table.

CREATE TABLE [dbo].[employee]
(
    employeeID        INT NOT NULL IDENTITY
    , username        VARCHAR(100) NOT NULL
    , usergroup        CHAR(1) NOT NULL
    , lastLogin        DATETIME
    , nameFirst NVARCHAR(50) NULL
    , nameLast NVARCHAR(80) NULL
    , CONSTRAINT PK_employeeID PRIMARY KEY CLUSTERED (employeeID)
    , CONSTRAINT U_username UNIQUE NONCLUSTERED (username)
)

The username field stores the values returned from SYSTEM_USER and since that's what I'm going to filter this table on I've added a UNIQUE index on it. Because the employeeID is the primary key and will be used as a foreign key throughout the rest of the database, I will want to be able to easily get to the empolyeeID value... and that leads us to create a function. The function below is very simple and simply looks up the SYSTEM_USER value for who is executing the function, queries the employee table, and returns their employeeID value.

CREATE FUNCTION [dbo].[getEmployeeID] ()
RETURNS INT 
WITH SCHEMABINDING
AS 
BEGIN
    DECLARE @employeeID INT
    SELECT @employeeID = employeeID FROM dbo.employee
        WHERE username = SYSTEM_USER
    RETURN @employeeID
END
GO

So now what?

While that might be nifty on it's own, you're probably asking "Ok, so what?" Did you know you could use the result of a function inline with regular T-SQL? Well, you can! Now that you know that, let's see an example of that in action. Below I have a reduced version of a view that uses the getEmployeeID function we created earlier, look for it in the WHERE clause. So anyone and everyone can have access to this view and it will always filter down to just one record for the employee who is logged in.

CREATE VIEW [dbo].[vCurrentEmployee]
WITH SCHEMABINDING
AS
SELECT e.employeeID, e.username, e.nameLast, e.nameFirst, e.usergroup, 
        e.lastLogin
    FROM dbo.employee e
    WHERE e.employeeID = dbo.getEmployeeID()
    WITH CHECK OPTION
GO

There's More?

While filtering results is cool, that's not the part that was causing me headaches. All data modifying actions are handled using stored procedures because then I can start a transaction, run whatever queries are needed and commit the transaction with the ability to ROLLBACK TRANSACTION if needed. But, that has always presented a bit of a challenge when tracking who does what especially when using MS Access. But, now I have the perfect (ok, nothing is perfect...) solution for that as well! Look at the INSERT statement in the stored procedure below for its usage. See how easy that is to use everywhere?

CREATE PROC dbo.surveyStart
    @gradID                INT = 0
    , @gradResponseID    INT OUTPUT

AS
BEGIN
SET NOCOUNT ON;

DECLARE @timestamp    DATETIME;

DECLARE @errMsg     VARCHAR(500);
DECLARE @errLocation    VARCHAR(50);
SET @errLocation = 'SP:  surveyStart';

SET @timestamp = GETDATE();

INSERT INTO dbo.gradResponse (gradID, employeeID, surveyStart) 
    VALUES (@gradID, dbo.getEmployeeID(), @timestamp);

SELECT @gradResponseID = SCOPE_IDENTITY();

END;

Final Thoughts

It took me a long time to find this SYSTEM_USER command and the difference has been huge. This is a much more elegant solution than my old way of achieving the same results. It makes writing new code easier and reduces room for error. The biggest benefit is that every bit of code that is not in Access can be placed easily in proper source control!