Skip to content

Tree Filters - Adding Row-wise User access

← Tips, Tricks & More
In many cases you may want to restrict user's access rights to different parts of the data like regions, companies etc. 


This can be achieved by using a Tree Filter and a small UserAccess table in SQL Server that controls each users access to items in the Tree Filter. The users access table can then easily be maintained by non-technical personal by creating a SQL Spreads document to maintain the data in the table. 

Follow the steps below to setup user access on a Tree Filter.

Creating the User Access table
We start with creating the SQL Server table that controls which parts of the data that a user has access to.
  1. Start with a blank Excel sheet and add the following columns: 
    - UserLogin - This will keep the Users Windows Login in the format Domain\UserName
    - Code - this will keep the Filter items key or code value that we want to give the user access to
    - TableToAccess - This will be the table that this user access row is valid for (by adding this column we can use the same access table for several filters or tables)


  2. Enter a few rows of data and then click the Table Creator button in the SQL Spreads tab in Excel.  


  3.  Select the database where the user access table should be created and give it a name like meta_UserAccess


  4. Click OK to create the table
Adding the User Access restriction to the Tree filter 
We restrict the items in the Tree filter by adding a new section to the SQL code that fetch the items in the Tree filter from the database:


For each filter, update the red marked parts below:


The complete Filter query with the user access restriction will then be: 
SELECT      [CompanyCode] AS                             Code,        -- This shows the items in the dim_Company table as a tree filter in SQL Spreads left-side panel
            [CompanyName] AS                            [Text]
FROM        [SQLSpreadsDemo].[dbo].[dim_Company] AS     company
INNER JOIN  [SQLSpreadsDemo].[dbo].[meta_UserAccess] AS access    
ON          company.[CompanyCode] = access.[Code]                    -- Join the rows to be shown in the tree filter with the access table to limit rows based on the current user
WHERE
            access.[TableToAccess] =                    'Company'    -- This parameter allows us to use the same access table for several tables
            AND access.[UserLogin] =                    @WindowsUser -- This SQL Spreads parameter will hold the authenticated users Windows login





Feedback and Knowledge Base

(thinking…)