Data Masking and Unmasking Strategy for Power BI Report Testers and Business Users

By:   |   Updated: 2022-11-07   |   Comments   |   Related: > Power BI


As a Power BI developer, I want to understand how to implement masking and unmasking database strategies for testing and publishing a Power BI reporting solution.


Using the masking and unmasking database feature for testing, implementation, and viewing the Power BI report ensures that the testers cannot view sensitive information. At the same time, this feature allows business users to view their sensitive data on the production site.

Role of Tester and Business User in a Reporting Solution

The role of a tester and a business user is very much the same in every reporting solution, including Power BI. However, there is a thin line between adopting a standard practice and defining principles that work for you.

We are particularly focused on a simple scenario of testing a Power BI report in this tip, but in no way does this simple scenario limit your capability of porting it to a full-fledged professional life scenario.

Let's discuss the simple roles of the tester and business user in a Power BI-centric reporting solution.


A Power BI tester is supposed to test a Power BI report to ensure that it serves the purpose, but at the same time, they are not supposed to see the sensitive data contained in the reporting visuals.

One might argue why we cannot use test data for the tester. The answer lies in the scenario itself, as we are in the final stage of testing the report just before deployment to production. The tester must connect with actual data because the report is about to be published to the target side, which is a specific type of scenario.

Business User

The business user is the user who is going to consume the reporting solution by viewing and analyzing the data in the form of a Power BI report.

The Need for Masking and Unmasking Data

Since we want the tester to test the report (data) without seeing the actual data (for specific sensitive visuals) and the business user to view the data without any trouble, we have to switch between masking and unmasking (sensitive) data. The tester does his job, and the report finally gets deployed to the designated workspace for the business user to consume.

Database Strategy to Mask and Unmask Data for Testing and Publishing

Now, masking and unmasking within the database itself achieve the desired objective based on the following points:

  1. We need to create a database user who can view the sensitive data in a masked form.
  2. The business user is also a database user who can view sensitive data in the unmasked form.
  3. The overall process should be transparent for the business user but should work as per the requirement.

Use Case Scenario

An example scenario is when the final checks are being performed. The tester can test the data but cannot view the sensitive column information, while the same column is viewable by the business user once it is deployed to the designated workspace.

Setup Sample Database (WatchesV5) with Tester (PowerBITester) and User (PowerBIUser) Logins

For this example, let's set up an on-premises SQL database but please feel free to get an Azure database setup for yourself if that is easier with Power BI in published mode. For an on-premises SQL database, an on-premises gateway must be installed on your machine (where the SQL instance resides) for your data refresh.

Note: This scenario assumes the reader knows how to connect Power BI with their database either on-premises or an Azure SQL database.

Let's set up a database called WatchesV5 along with two logins for the tester and user by running the following script against the master database:

--USE [master]
-- (1) Create WatchesV5 database
-- (2) Create PowerBITester Login first in master database
-- (3) Create PowerBIUser Login first in master database

Setup PowerBITester and PowerBIUser Users Based on Logins and Populate Sample Database with Watch Table

Create two users in the sample database based on the logins just created in the master database, and populate the sample database WatchesV5 by running the T-SQL script against it:

--USE WatchesV5
-- (1) Connect to the database and create user for the same login
-- (2) Connect to the database and create user for the same login
-- (3) Creating Watch table with masked Brand column
CREATE TABLE [dbo].[Watch]
    [WatchId] INT NOT NULL IDENTITY(1,1),
    [WatchType] VARCHAR(40),
    [Brand] VARCHAR(40) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
    [Colour] VARCHAR(20),
    [Price] DECIMAL(10,2)
    CONSTRAINT [PK_WatchType] PRIMARY KEY (WatchId),
-- (4) Populating Watch table
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (1, N'Digital', N'Timex Ironman ', N'Black', CAST(150.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (2, N'Analogue', N'Casio G-Shock', N'Blue', CAST(200.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (3, N'Smart', N'Garmin Smartwatch', N'Yellow', CAST(300.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (4, N'Sports', N'Timex Expedition', N'Black', CAST(150.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (5, N'Digital', N'Seiko Superrunner', N'Brown', CAST(185.00 AS Decimal(10, 2)))

Mask Data (Watch Table) for Tester and Unmask for Business User

Now, by default, when we created the Watch table with a masked column, the data is masked for both users. But now, we can unmask data for PowerBIUser.

This is achieved by running the following script against the sample database:

--Grant SELECT on Watch table
Grant SELECT ON Watch TO PowerBITester
--Grant SELECT on Watch table
Grant SELECT ON Watch TO PowerBIUser

Test PowerBI User Access

Let's now test PowerBIUser access by running the following T-SQL script:

-- Testing PowerBIUser access
SELECT * FROM dbo.Watch

The output is as follows:

Watch Table with Brand column clearly readable such as Timex Ironman, Casio G-Shock, Garmin, Timex etc. for PowerBIUser

Test PowerBI Tester Access

Now, let's see from the view of a PowerBI tester, who is not supposed to view the sensitive information, i.e., the brand of watch in this case:

SELECT * FROM dbo.Watch

The results are as follows:

Watch Table with Brand column not readable such as Txxxx, Cxxxx,Gxxxx,Txxxx etc. for PowerBITester

Database Hosting Choice Before Publishing to Power BI

Next, we will build a report on top of our database and see the difference between when the Power BI Tester uses it and when the Power BI User views it.

Note: Remember you have two options: connect your Power BI report with an on-premises database like the one we built or with an Azure SQL database.

Most importantly, if you want to build an on-premises database for your Power BI report, then after you publish the report, you must enable the on-premises gateway for the Power BI report dataset to comfortably connect with your on-premises database for refreshing purposes. This is a common practice when using on-premises data sources:

On-premises data gateway installation window

Create Power BI Report and Link with Sample Database (WatchesV5)

Note: This tip assumes that Power BI Desktop is installed on your machine and that you are familiar with creating Power BI reports.

Let's build a Power BI report called Watches Report and connect it to the sample database WatchesV5.

Open Power BI Desktop, click on Get Data, and enter the server and database name to get connected:

SQL server database credentials with server name and Database name: WatchesV5

Next Load data:

Loading data from Watch table by clicking on Load button

Pick Table visual and add it to the canvas to view the data:

Viewing Watch table in Power BI Desktop

On the Home tab, click on Transform data and select Data source settings:

Clicking on Data source settings to edit the setttings

Click Edit Permissions… followed by clicking Edit…as shown below:

Clicking on Edit button of Data source settings=>Edit Permissions

Now make changes by filling in PowerBITester details and save the changes as follows:

Inputting PowerBITester username and password we chose earlier

Refresh the report now to see how the visual is masking the watch brand for PowerBITester after we gave permission to work on the report:

The report view by tester clearly shows the brand column is masked

The tester is given this report to test and gives the green signal if all is well.

Publishing the Report

Once the tester gives the green signal, one of two options can happen:

  1. Another team publishes the report that replaces the tester credentials with business user credentials who can view all the data of the report, while the tester has no access to that workspace, or
  2. The tester can publish the report to a test site, and once the tester is happy, the report can be published to the production site

Once published, the report shows the visual below (as it is using PowerBITester credentials to connect with the sample database):

Report view with masked data for tester

Replacing Tester Credentials with User

Go to the report workspace. Click on the dataset settings:

Clicking on Settings of Watches Report dataset

Click on Edit credentials under Data source credentials:

Editing credentials

Add PowerBIUser credentials:

Adding PowerBIUser user name and password to connect to report data (on-premises database)

Refresh the dataset and the report:

Refreshing the dataset
Brand column of the report visual is clearly viewable by the user after we changed the credentials.

Congratulations, you have just learned how to mask and unmask report data for different types of users.

Note: A more resilient data view control can be established through RLS (row level security), especially when you have different levels of business users who will consume the report data.

Next Steps

Learn more about Power BI in this 3 hour training course.

Click here to start the Power BI course

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Haroon Ashraf Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-11-07

Comments For This Article

get free sql tips
agree to terms