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.

If the access rules rarely change, a simpler option is to write them into the filter itself. See the fixed setup in Filtering rows from SQL Server.

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

Adding group-based user access restriction to the Tree filter

Instead of assigning access to individual users, you can use Windows Active Directory groups or Microsoft Entra ID groups. This can make access easier to manage, especially in larger organizations where users are already organized into security groups.

For example, the UserLogin column in the meta_UserAccess table can contain group names instead of individual users:

SQL Server and Azure SQL provide the IS_MEMBER() function to check whether the current authenticated database user is a member of a Windows Active Directory or Microsoft Entra ID group.

The Tree Filter query can therefore be changed to:

SELECT      [CompanyCode] AS                         Code,
            [CompanyName] AS                        [Text]
FROM        [SQLSpreadsDemo].[dbo].[dim_Company] AS company
INNER JOIN  [SQLSpreadsDemo].[dbo].[meta_UserAccess] AS access
ON          company.[CompanyCode] = access.[Code]
WHERE
            access.[TableToAccess] = 'Company'
            AND IS_MEMBER(access.[UserLogin]) = 1

With this setup, users can be added to or removed from the appropriate group in Active Directory or Microsoft Entra ID instead of maintaining access for every individual user in the meta_UserAccess table.