May 16, 2016

Say you have a company with multiple territories – Northwest, Northeast, Central, Southwest, and Southeast. (For this example, I’m using MS AdventureWorks 2012 sample database.)

You have three employees: Jim, Dan and Adam.

Jim should see data for all territories
Dan should see data for the Northwest territory
Adam should see data for the Southwest and Southeast territories.

Most ERP packages allow you to set up security so that each user can only see the data for his/her division when they use the application.

The problem: Generally, that security isn’t defined at the database level. So, if you use a tool like SSRS, if Dan has access to one region’s sales report, he has access to all region sales reports. Which isn’t want you want.

SQL Server 2016 has an advanced feature where you can establish row level security at the database level. But it’s newly released and has its issues. It also doesn’t apply to my customers.

Further, using it with an ERP would require modifying the underlying database of your ERP software, and I DON’T LIKE DOING THAT.

The Workaround Solution

Fortunately, there’s a workaround.

We start by creating a table that states which employees have access to which territories. It can be as simple as this:

Basically, we create this table so we can use it for different security scenarios, rather than just one report. The “ObjectName” field allows us to secure reports based on different values.

For example, we may want to secure one report by sales territories and another by cost center. So, we can create records with object name set to either “TERRITORY” or “COST CENTER.” In this example, we’ll use “TERRITORY.”

Then, we populate the table with data:

We can now use this with a sample query to show total sales by territory:

The last join takes “TERRITORY” and joins it to the ObjectsSecured table based on the name.

We run the query three times and copy the results into Excel. As you can see, each person only receives data for the assigned territories:

Now, let’s move to SSRS. Here’s the sample report:

We want to use the built-in field called “User ID.” Unfortunately, we can’t use it directly in our query as the system won’t recognize it. (Certainly, I haven’t gotten it to work.)

Instead, we create a hidden parameter that we’ll pass to the query. Here’s the basic definition (shown as hidden):

Now, we go to default values. The default value is set as an expression:

And now the expression:

You’ll note I’ve done something a little fancy here. “UserID” will deliver the domain name as well as the user ID. As domain names can change (and user names are always unique for my customers), I prefer to keep security on a name only basis.

Now that we have our parameter, we can use it in the query: