Adding Row-wise User access to a Tree filter

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 User Access table in SQL Server that controls each user's access to items in the Tree Filter. The users access table can then easily be maintained by non-technical personnel 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 User's 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)
  
  To quickly create the table, you can copy the table below into Excel:

2. Enter a few rows of data and then click the SQL Server Tools button in the SQL Spreads tab in Excel and then select the New SQL Server table from Excel option.
   

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 fetches 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 user's Windows login