---
title: "Add Row-Wise Access to Tree Filters"
slug: "tree-filters-adding-row-wise-user-access"
description: "Restrict which rows each user can see by pairing a Tree Filter with a user access table in SQL Server, maintained in Excel by non-technical staff."
updated: 2026-11-05T16:49:10Z
published: 2026-11-05T16:49:10Z
canonical: "sqlspreads.com/tree-filters-adding-row-wise-user-access"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://sqlspreads.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# 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.

![Tree filter.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Tree%20filter.png)

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](https://sqlspreads.com/docs/filtering-rows-from-sql-server#show-each-user-only-their-rows-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)  

![meat_UserAccess table.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/meat_UserAccess%20table.png)  

To quickly create the table, you can copy the table below into Excel:

| UserLogin | Code | TableToAccess |
| --- | --- | --- |
| OBNEX\FHO | EL | Company |
| OBNEX\MJO | AU | Company |

1. 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. ![SQL Spreads Click on SQL Server Tools to create a SQL Server table](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/SQL%20Spreads%207.6.3%20Click%20on%20SQL%20Server%20Tools%20to%20create%20a%20SQL%20Server%20table.png)
2. Select the database where the user access table should be created and give it a name like meta_UserAccess. ![Create table.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Create%20table.png)
3. 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:

![Add user access to Filter.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Add%20user%20access%20to%20Filter.png)

For each filter, update the red marked parts below:

![Filter query match table.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Filter%20query%20match%20table.png)

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
```

Note:

The row-wise security will always use the Windows Login name of the authenticated user that is running Excel, so it will be independent of how you connect to SQL Server. The parameter @WindowsUser that is used in the example contains the authenticated Windows user's name (in the format DOMAIN\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:

| UserLogin | Code | TableToAccess |
| --- | --- | --- |
| CONTOSO\Sweden-Finance | SE | Company |
| CONTOSO\Germany-Finance | DE | Company |

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.

Note:

This only works when using **Windows Authentication** or **Microsoft Entra ID authentication** to connect to the database. IS_MEMBER() checks membership in the corresponding Windows AD or Microsoft Entra groups.