Adding Row-wise User access to a Tree filter
    • Dark
    • PDF

    Adding Row-wise User access to a Tree filter

    • Dark
    • PDF

    Article Summary

    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

    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)
      meat_UserAccess table.png
      To quickly create the table, you can copy the table below into Excel:
    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.
      Row-wise security 7.1

    2. Select the database where the user access table should be created and give it a name like meta_UserAccess.
      Create table.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

    For each filter, update the red marked parts below:

    Filter query match table.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
                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

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

    Was this article helpful?