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.
- 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)
- Enter a few rows of data and then click the Table Creator button in the SQL Spreads tab in Excel.
- Select the database where the user access table should be created and give it a name like meta_UserAccess
- Click OK to create the table
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
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