In this post we are going to cover row-level security and governance in Power BI: static and dynamic RLS, who RLS applies to, object-level security, workspace roles and sharing, and the governance features like sensitivity labels and endorsement. These are 7 real Power BI interview questions, and the last one is a scenario a manager asks: a regional manager says she can see other regions' sales.
Security is the topic where a wrong answer can cost a company real trouble. One report, many users, and each person must see only their own data. Interviewers ask about it because the details matter: where roles are defined, who bypasses them, and how you prove the filter works.
Every example below runs on a small sample data set, so you can rebuild it in Power BI Desktop and check each number yourself.
Part 8 of the Real Time Power BI Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: What is row-level security, and what is the difference between static and dynamic RLS?
- Question 2: How do you implement dynamic RLS with USERPRINCIPALNAME()?
- Question 3: Where are RLS roles defined and members assigned, and who bypasses RLS?
- Question 4: What is object-level security (OLS), and how is it different from RLS?
- Question 5: What are the workspace roles, and how do you share content: apps, direct sharing or Build permission?
- Question 6: How do you govern Power BI content with sensitivity labels, endorsement, tenant settings and lineage?
- Question 7: Scenario - a regional manager says she can see other regions' sales. How do you investigate and fix it?
- The full SQL script
The short answer
RLS filters rows with DAX rules in roles, static or dynamic with USERPRINCIPALNAME(), and applies only to Viewers. OLS hides tables and columns. Share with apps, and govern with sensitivity labels, endorsement, tenant settings and lineage.
Keep that summary in mind. Every question below is one piece of it.
Let's create some sample data
We use a tiny shop: 5 products, 4 customers in two regions, a date table for 2025 and 2026, and 12 sales in January to March of each year. The script below is written for PostgreSQL (the date table uses generate_series), and it works on any database with small changes. Load the four tables into Power BI Desktop and relate fact_sales to each dimension on its key.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);Here are the 12 sales. Each row is one sale line: a date, a product, a customer, a quantity and a unit price.
| sale_id | order_date | product_key | customer_key | qty | unit_price |
|---|---|---|---|---|---|
| 1 | 2025-01-10 | 1 | 1 | 2 | 40.00 |
| 2 | 2025-01-22 | 3 | 2 | 1 | 250.00 |
| 3 | 2025-02-05 | 2 | 3 | 5 | 10.00 |
| 4 | 2025-02-18 | 5 | 4 | 1 | 60.00 |
| 5 | 2025-03-03 | 4 | 1 | 2 | 50.00 |
| 6 | 2025-03-27 | 3 | 3 | 1 | 250.00 |
| 7 | 2026-01-08 | 1 | 2 | 3 | 40.00 |
| 8 | 2026-01-25 | 3 | 1 | 1 | 250.00 |
| 9 | 2026-02-11 | 2 | 4 | 4 | 10.00 |
| 10 | 2026-02-20 | 4 | 3 | 2 | 50.00 |
| 11 | 2026-03-09 | 5 | 2 | 2 | 60.00 |
| 12 | 2026-03-30 | 3 | 4 | 2 | 250.00 |
And the customers. Notice the email column: we reuse those emails as user names when we build dynamic RLS in Question 2.
| customer_key | customer | city | region | |
|---|---|---|---|---|
| 1 | Sara Khan | Toronto | East | sara@tbshop.com |
| 2 | Omar Ali | Dallas | West | omar@tbshop.com |
| 3 | Lina Park | Seattle | West | lina@tbshop.com |
| 4 | Mei Chen | Boston | East | mei@tbshop.com |
Question 1: What is row-level security, and what is the difference between static and dynamic RLS?
Row-level security (RLS) filters which rows a user can see, so everyone can use the same report. You create a role and give it a DAX filter on a table.
Let's write a static rule. The role East gets the rule region equals East on the Customer table.
// Modeling > Manage roles
// Role: East Table: Customer
[Region] = "East"The filter flows from Customer to Sales through the relationship. Without RLS, everyone sees 1920.00. Here is what a user in the East role sees.
| customer | city | sales |
|---|---|---|
| Mei Chen | Boston | 600.00 |
| Sara Khan | Toronto | 430.00 |
| Total (East) | 1030.00 |
Check this result yourself with SQL
The role filter on Customer flows to Sales, so an East user sees 1030 instead of the full 1920.
SELECT customer, city, sales FROM (
SELECT c.customer, c.city, SUM(f.qty * f.unit_price) AS sales, 0 AS srt
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
WHERE c.region = 'East'
GROUP BY c.customer, c.city
UNION ALL
SELECT 'Total (East)', '', SUM(f.qty * f.unit_price), 1
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
WHERE c.region = 'East') t
ORDER BY srt, customer;Only the two East customers are left: Mei Chen with 600.00 and Sara Khan with 430.00, so the total is 1030.00 instead of 1920.00.
That is static RLS: the value is hard-coded, so you need one role per region. Dynamic RLS uses one role for everyone, and the rule looks up the signed-in user to decide what each person sees.
| Static RLS | Dynamic RLS | |
|---|---|---|
| RULE | [Region] = "East" | [Email] = USERPRINCIPALNAME () |
| ROLES | one per value: East, West ... | one role for everyone |
| NEW USER | add to a role (or a new role) | add a row to a mapping table |
Static is fine for a handful of fixed groups. With many users, or a mapping that changes often, go dynamic: adding a user becomes adding a row, not a new role.
Question 2: How do you implement dynamic RLS with USERPRINCIPALNAME()?
You build a security mapping table with one row per user and region, create one role with a filter on that table, and let USERPRINCIPALNAME() pick the rows for whoever is viewing.
Here is the mapping table. In our sample, Sara and Mei map to East, Omar and Lina map to West.
| region | |
|---|---|
| mei@tbshop.com | East |
| sara@tbshop.com | East |
| lina@tbshop.com | West |
| omar@tbshop.com | West |
Now the role. The rule says email equals USERPRINCIPALNAME(), which returns the sign-in name of the viewer, usually their email address.
// Role: Regional Table: UserRegion
[Email] = USERPRINCIPALNAME ()
// UserRegion[Region] filters Customer[Region]
// Customer filters SalesRelate the mapping table to Customer on region, as a many-to-many relationship filtering in one direction. The filter then flows from UserRegion to Customer, and on to Sales. Here is what Omar sees when he opens the report.
| category | sales |
|---|---|
| Electronics | 600.00 |
| Accessories | 170.00 |
| Home | 120.00 |
| Total | 890.00 |
Check this result yourself with SQL
USERPRINCIPALNAME() returns omar@tbshop.com, the mapping keeps West, and Omar sees 890 of the 1920.
WITH user_region (email, region) AS (
VALUES ('sara@tbshop.com', 'East'), ('mei@tbshop.com', 'East'),
('omar@tbshop.com', 'West'), ('lina@tbshop.com', 'West'))
SELECT category, sales FROM (
SELECT p.category, SUM(f.qty * f.unit_price) AS sales, 0 AS srt
FROM fact_sales f
JOIN dim_customer c ON c.customer_key = f.customer_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE c.region IN (SELECT region FROM user_region WHERE email = 'omar@tbshop.com')
GROUP BY p.category
UNION ALL
SELECT 'Total', SUM(f.qty * f.unit_price), 1
FROM fact_sales f
JOIN dim_customer c ON c.customer_key = f.customer_key
WHERE c.region IN (SELECT region FROM user_region WHERE email = 'omar@tbshop.com')) t
ORDER BY srt, sales DESC;Omar sees only West: Electronics 600.00, Accessories 170.00, Home 120.00, and 890.00 in total out of the full 1920.00.
Two tips for the interview:
- Use USERPRINCIPALNAME(), not USERNAME(). In Desktop, USERNAME() returns DOMAIN\user.
- A manager who covers two regions simply gets two rows in the mapping table.
Question 3: Where are RLS roles defined and members assigned, and who bypasses RLS?
You define roles and their DAX rules in Desktop, under Modeling, Manage roles, or in web modeling in the service. Then you publish the model. You assign members in the service, in the semantic model's security settings: users or security groups.
Now the part interviewers really want. RLS only filters people with view access.
| Workspace role | RLS applied? |
|---|---|
| Admin | No: sees all data |
| Member | No: sees all data |
| Contributor | No: sees all data |
| Viewer / app user | Yes: filtered |
Admins, Members and Contributors can edit the model, so they see all the data. Only Viewers and app users are filtered.
A few more rules worth saying out loud:
- Security groups are supported. Microsoft 365 groups are not.
- Service principals can't be members of a role.
- Roles are additive: someone in two roles sees both sets of rows.
- A user in no role sees no data at all.
To test, use View as in Desktop or Test as role in the service, then confirm with a real test user. View as uses your own identity, so it can't simulate a guest user.
Question 4: What is object-level security (OLS), and how is it different from RLS?
RLS answers "which rows?". OLS answers "can this user see this table or column at all?". For users in the role, the object simply does not exist: it is not in the field list, and a visual that uses it shows an error that the field can't be found.
A classic use is hiding a salary column. In our sample, we hide the customer email column from sales reps, while they still see the rest of the Customer table. You set this in TMDL view in Desktop, or with an external tool like Tabular Editor, by setting the permission to none. Here is one role that combines both: a row filter on Customer, plus OLS on the email column.
role 'Sales Rep'
modelPermission: read
tablePermission Customer = [Region] = "East"
columnPermission Email = none| RLS | OLS | |
|---|---|---|
| HIDES | rows | tables, columns |
| SET IN | Manage roles | TMDL view, Tabular Editor |
| USER SEES | fewer rows | field not found |
Like RLS, OLS restricts Viewers.
Design pages carefully. A visual built on a hidden column breaks for the users who can't see that column.
Question 5: What are the workspace roles, and how do you share content: apps, direct sharing or Build permission?
There are four workspace roles.
| Role | Can do |
|---|---|
| Admin | manage workspace and access |
| Member | add people, share, publish app |
| Contributor | create, edit, schedule refresh |
| Viewer | view only; RLS applies |
For sharing, pick the tool by audience:
- App for a big audience. There is one app per workspace, and inside it you can create up to 25 audiences, each seeing different reports.
- Direct sharing, with a link or with named people, for a few people and ad hoc requests. It does not scale and it is harder to audit.
- Build permission on the semantic model, so analysts can create their own reports and use Analyze in Excel on your model without joining the workspace.
In a shared capacity workspace, every user needs Pro or Premium Per User. On F64 or larger, Viewers can use a free license.
Question 6: How do you govern Power BI content with sensitivity labels, endorsement, tenant settings and lineage?
Governance has four parts, and a good answer names all four.
Sensitivity labels come from Microsoft Purview. A label can be inherited from sources like Azure SQL and Synapse, flows down to downstream items, and travels with exports to Excel, PDF, PowerPoint and PBIX. CSV exports are not protected. And in the service, a label alone does not block access.
Endorsement tells people what to trust.
| Endorsement | Who sets it |
|---|---|
| Promoted | anyone with write permission |
| Certified | reviewers set by an admin |
| Master data | designated users, data items |
Master data marks core data items, like a customer semantic model, as the authoritative source.
Tenant settings in the admin portal apply to the whole organization: for example, who can export data, who can publish to the web, and who can use Copilot.
Lineage view shows where every item gets its data.
Before you change a semantic model, run impact analysis to see every report, dashboard and workspace that depends on it.
Question 7: Scenario - a regional manager says she can see other regions' sales. How do you investigate and fix it?
This is the scenario managers love. Our East manager should see 1030.00. Here is what she sees when RLS is not filtering her.
| region | sales |
|---|---|
| East | 1030.00 |
| West | 890.00 |
| Total | 1920.00 |
Check this result yourself with SQL
Without RLS she sees West too (890) and the grand total 1920; with the East role she should see 1030 only.
SELECT region, sales FROM (
SELECT c.region, SUM(f.qty * f.unit_price) AS sales, 0 AS srt
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.region
UNION ALL
SELECT 'Total', SUM(f.qty * f.unit_price), 1 FROM fact_sales f) t
ORDER BY srt, region;She sees West too, 890.00, and the grand total of 1920.00. Here is how to work through it, in order:
- Check her workspace role. If she is an Admin, Member or Contributor, RLS does not apply to her at all. This is the most common cause. Make her a Viewer, or give her access through the app.
- Check role membership. Is she really in the East role, and not also in a broader one through a security group? Roles are additive, so one extra role opens everything.
- Check that the filter reaches Sales. If the rule's table only reaches Sales against the relationship direction, for example through a bridge table, turn on apply security filter in both directions.
- Test. Use View as in Desktop, or Test as role in the service. She should see East, 1030.00, not 1920.00.
- Check the source model. If the report uses a live connection to Analysis Services or another semantic model, the roles live in that model. Fix them there, then confirm with her own login.
Start with the workspace role. Saying that first tells the manager you know RLS only applies to Viewers.
Key points to remember
- RLS filters rows: static rules or dynamic with USERPRINCIPALNAME()
- RLS applies only to Viewers; editors bypass it
- OLS hides tables and columns (TMDL view)
- Share with apps; Build for self-service
- Labels, endorsement, tenant settings, lineage
The full SQL script
If you want to rebuild everything at once, here is the whole script: the sample data and the SQL check behind every result above.
Show the full SQL script (sample data and every check)
-- ========================================================================
-- Power BI Interview Q&A #8 - Row-level security and governance
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA pbi_q08; SET search_path TO pbi_q08;
-- SQL Server: write DATE '2026-06-01' as CAST('2026-06-01' AS DATE).
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);
-- Step 1: Static RLS: what a user in the East role sees (rule [Region] = "East" on Customer)
SELECT customer, city, sales FROM (
SELECT c.customer, c.city, SUM(f.qty * f.unit_price) AS sales, 0 AS srt
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
WHERE c.region = 'East'
GROUP BY c.customer, c.city
UNION ALL
SELECT 'Total (East)', '', SUM(f.qty * f.unit_price), 1
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
WHERE c.region = 'East') t
ORDER BY srt, customer;
-- Step 2: Dynamic RLS: what omar@tbshop.com sees through the user-region mapping table
WITH user_region (email, region) AS (
VALUES ('sara@tbshop.com', 'East'), ('mei@tbshop.com', 'East'),
('omar@tbshop.com', 'West'), ('lina@tbshop.com', 'West'))
SELECT category, sales FROM (
SELECT p.category, SUM(f.qty * f.unit_price) AS sales, 0 AS srt
FROM fact_sales f
JOIN dim_customer c ON c.customer_key = f.customer_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE c.region IN (SELECT region FROM user_region WHERE email = 'omar@tbshop.com')
GROUP BY p.category
UNION ALL
SELECT 'Total', SUM(f.qty * f.unit_price), 1
FROM fact_sales f
JOIN dim_customer c ON c.customer_key = f.customer_key
WHERE c.region IN (SELECT region FROM user_region WHERE email = 'omar@tbshop.com')) t
ORDER BY srt, sales DESC;
-- Step 3: Scenario: what the manager sees when RLS does not filter her (all regions)
SELECT region, sales FROM (
SELECT c.region, SUM(f.qty * f.unit_price) AS sales, 0 AS srt
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.region
UNION ALL
SELECT 'Total', SUM(f.qty * f.unit_price), 1 FROM fact_sales f) t
ORDER BY srt, region;
Part 9: Deployment, Git and Refresh
The sample data on this page is invented for teaching. The result tables were produced by running the same calculation in SQL on PostgreSQL 16 over that sample data (open "Check this result yourself" under any result), so you can rebuild every number in Power BI Desktop and compare.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.