Warehouse SQL Granular Security

When SQL permissions are the right tool

A workspace role or an item permission opens a whole warehouse. Often that is too much: an analyst should query two views, a partner should not see the credit card column, and each sales representative should see only their own orders. When the default permissions from workspace role assignment or item permissions do not give you the level of control you need, use standard SQL constructs in a Fabric warehouse or SQL analytics endpoint for finer-grained access control.

There are three levels, and each has its own T-SQL construct.

These constructs work in a Warehouse and in the SQL analytics endpoint of a lakehouse. They have one limit that decides many scenarios: these permissions only apply to queries made through SQL.

Dynamic data masking is the fourth SQL protection, and it has its own lesson.

Connect first, then query

SQL permissions decide what a user can query. They do not let the user in. For a user to connect to a Fabric warehouse or SQL analytics endpoint, you must assign them to a workspace role or grant them the item Read permission. Without Read permission at a minimum, the connection fails.

The item Read permission alone opens the door and nothing else. When a warehouse is shared with no additional permissions, the recipient receives Read, which only allows the recipient to connect to the SQL analytics endpoint. The recipient cannot query any table or view or execute any function or stored procedure unless they are provided access to objects within the warehouse using the T-SQL GRANT statement.

The sharing options are the subject of the lesson on item permissions and sharing. One of them is the pattern for granular access. If you want to provide granular access to specific objects within the warehouse, share the warehouse with no additional permissions, then provide granular access to specific objects using the T-SQL GRANT statement.

The order can also be reversed, so that the user never connects with wrong permissions. To set up a user's granular permissions before they connect to the warehouse, define the permissions in SQL first. Then give them access by assigning a workspace role or granting item permissions.

One statement from other SQL platforms is missing. You cannot explicitly run CREATE USER in a Fabric warehouse or SQL analytics endpoint. When you run GRANT or DENY, Fabric creates the database user automatically. The user cannot connect until they also have sufficient workspace-level rights.

Object-level security: GRANT, REVOKE, DENY, and roles

Object-level security answers which objects a user can use. You manage object-level security with the GRANT, REVOKE, and DENY T-SQL statements. They are the Data Control Language commands of T-SQL.

The general concept is to GRANT some permission ON some object TO some user or role:

GRANT SELECT ON OBJECT::Person.Address TO [RosaQdM@contoso.com];
GRANT SELECT ON Person.Address TO [RosaQdM@contoso.com];

Both lines grant SELECT on the table Person.Address to one Microsoft Entra user. The OBJECT phrase is optional if the schema name is specified, so the second line is the usual form. For stored procedures and functions, use GRANT EXECUTE to assign permissions.

The three statements have the same shape. DENY ends with TO and the principal, and REVOKE accepts FROM or TO. A permission can also target a whole schema, with the class SCHEMA and the scope qualifier ::, as in GRANT SELECT ON SCHEMA::Sales TO Vendors.

DENY and REVOKE both take access away, but they are not the same.

DENY wins. DENY takes precedence over all permissions, except that it does not apply to object owners. A user who is in a role with SELECT on a table, and who is denied SELECT on that table, cannot read it.

REVOKE only removes. It removes a permission that was granted or denied to that principal. To stop one member of a role, deny the permission to that member. A REVOKE for that member has nothing to remove, because the grant belongs to the role.

The scope of a DENY matters when you grant again.

One exception is kept for backward compatibility: a table-level DENY does not take precedence over a column-level GRANT.

SQL roles, and checking the result

You do not have to grant user by user. You can assign users to SQL roles, both custom database roles and built-in database roles. For simplicity of management, assigning permissions to roles is preferred to using individuals.

CREATE ROLE SalesReaders;
GRANT SELECT ON sales.Orders TO SalesReaders;
ALTER ROLE SalesReaders ADD MEMBER [charlie@contoso.com];
ALTER ROLE SalesReaders DROP MEMBER [charlie@contoso.com];

CREATE ROLE creates a new database role in the current database. After you create a role, configure its permissions by using GRANT, DENY, and REVOKE. To add members to a database role, use ALTER ROLE with ADD MEMBER. DROP MEMBER removes a member from the role.

A member can be a database user or a user-defined database role. ALTER ROLE has one more form, WITH NAME, which changes the name of a user-defined database role.

After granting, verify. A connected user can view the permissions available to them by using the sys.fn_my_permissions function, at three scopes:

SELECT * FROM sys.fn_my_permissions(NULL, 'Database');
SELECT * FROM sys.fn_my_permissions('<schema-name>', 'Schema');
SELECT * FROM sys.fn_my_permissions('<schema-name>.<object-name>', 'Object');

The first query lists the permissions of the current user on the database, the second on one schema, and the third on one object.

A user with elevated permissions can list the permissions granted explicitly to users by joining the system views sys.database_principals and sys.database_permissions. Read the result with care: this query does not return permissions that users receive through a Fabric workspace role or item. A user who is missing from the result can still read everything as a workspace Contributor.

Column-level security: GRANT SELECT with a column list

Column-level security protects a sensitive column inside a table that the user may otherwise read. You implement column-level security with the GRANT T-SQL statement, by adding a list of columns after the table name.

Take a Customers table with the columns CustomerID, FirstName, CreditCard, LastName, Phone, and Email. This statement lets Charlie read every column except CreditCard:

GRANT SELECT ON Customers(CustomerID, FirstName, LastName, Phone, Email) TO [Charlie@contoso.com];

The statement grants SELECT only on the five columns between the parentheses. CreditCard is not in the list, so Charlie has no permission on it.

The effect is an error, not a silent filter. Queries executed as Charlie fail if they include the CreditCard column:

SELECT * FROM Customers;
-- Msg 230: The SELECT permission was denied on the column 'CreditCard' of the object 'Customers'

Four facts complete the picture of column-level security in a warehouse.

• Column-level security is simpler than designing additional views to filter out columns.
• Assigning the column permissions to roles is preferred to using individuals.
• Only Microsoft Entra authentication is supported.
• Column-level security is applied to a shared warehouse or lakehouse accessed through a SQL analytics endpoint, because the underlying data source has not changed.

The restriction lives in the database tier, not in an application. The database applies the access restrictions every time data access is attempted, from any application or reporting platform including Power BI.

Row-level security: the predicate function

Row-level security lets workers see only the rows of their department, or lets each customer of a multitenant solution see only the rows of their company. You implement RLS by using the CREATE SECURITY POLICY Transact-SQL statement, and predicates created as inline table-valued functions.

So there are two objects. The function holds the logic, and the policy connects the function to a table. Start with the function:

CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'manager@contoso.com';

Read it in three parts. The function receives one value, the SalesRep of a row. It is an inline table-valued function, created WITH SCHEMABINDING. And its WHERE condition is the security predicate. Security predicates are conditions that determine which rows a user can access. Here a user can access the rows where the SalesRep column, mapped to the @SalesRep parameter, matches the result of USER_NAME(). The second part of the condition is an exception for one user name, the manager.

The function USER_NAME() is what makes one definition serve every user. Each sales representative sees their own rows, and the manager sees all of them.

Row-level security: the security policy

The function does nothing until a policy uses it. The security policy invokes and enforces the function on a table:

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON sales.Orders
WITH (STATE = ON);

The statement names the policy, adds the function as a filter predicate, passes the SalesRep column of the table as the argument, names the table sales.Orders, and turns the policy on. A table that has a security predicate defined but disabled is not filtered.

Filter predicates silently filter the rows available to read operations. The application is unaware of rows that are filtered from the result set, and if all rows are filtered, a null set is returned. No error tells the user that rows are missing.

The filter covers more than SELECT.

Two limits of scope are easy to forget. Each table must have its own row-level security defined separately. And users who query tables without a row-level security policy view unfiltered data.

In a SQL analytics endpoint the tables already exist, and the work is the same. In the SQL analytics endpoint, you cannot CREATE TABLE, but you can CREATE SCHEMA, CREATE FUNCTION, and CREATE SECURITY POLICY.

Who the policy applies to, and who can create it

A SQL security policy has no privileged readers. Security policies apply to all users, including dbo users in the database. If members of roles like Administrator, Member, or Contributor need to see all rows to troubleshoot or validate data, the security policy must be written to allow that.

This is why the example function has a second condition for the manager. Take the function without it. For an Admin whose name is in no SalesRep value, the condition is false on every row, so all rows are filtered, and if all rows are filtered, a null set is returned.

Creating the policy needs specific permissions.

Users who only query the table need nothing more than their table permission. If a security policy is created with SCHEMABINDING = ON, which is the default, the permission checks on the predicate function are bypassed when users query the target table.

Row-level security hides rows from a direct query, but a careful user can still learn values from errors. A query such as SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' returns a divide-by-zero exception only when the salary is exactly 100,000, so the error reveals the value. Limit permissions and monitor suspicious activity, such as constantly changing policies and predicate functions.

Change a predicate that is in use

A predicate function that is bound to a policy is protected. Attempts to modify a function that is used as a predicate on a table within a schema-bound security policy result in an error. To modify a row-level security function, you must first drop the security policy.

The change is a sequence of three steps:

DROP SECURITY POLICY SalesFilter;
ALTER FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50)) ...
CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep) ON sales.Orders WITH (STATE = ON);

Drop the policy, alter the function with the new condition, and create the policy again. Between the first and the last step the table has no policy, and users who query tables without a row-level security policy view unfiltered data.

The binding protects the table too. Attempts to alter the schema of a table bound by a schema-bound security policy result in an error. However, columns not referenced by the predicate can be altered.

The effect on Power BI Direct Lake

SQL security has a cost for reports, and it appears as a change of storage mode. Row-level security and column-level security only apply to queries on a Warehouse or SQL analytics endpoint in Fabric.

The result is a fallback. Power BI queries on a warehouse in Direct Lake mode fall back to Direct Query mode to abide by row-level security. The same happens to abide by column-level security.

For a report on a lakehouse the rule is general. If any security is defined in the SQL analytics endpoint that the report is built on, the queries automatically fall back to DirectQuery mode. If you do not want this fallback, you can create a new lakehouse using shortcuts to the tables in the original lakehouse and not define RLS or OLS in SQL on the new lakehouse.

SQL security or OneLake security

Both models can restrict rows, columns, and objects. They differ in where the restriction is enforced.

OneLake security applies its restrictions consistently across all compute engines. So when the same users read a lakehouse table from Spark and from SQL, a SQL policy protects only one of the two paths.

A warehouse has a related leak. Security policies defined using SQL constructs in a warehouse are enforced only within the SQL execution context of the warehouse. When you access data from a warehouse through shortcuts in OneLake, these SQL security semantics are not translated into OneLake security policies. Users accessing the data through a shortcut might see the full warehouse data.

In the SQL analytics endpoint of a lakehouse, the access mode decides which of the two models governs tables.

In delegated identity mode, OneLake security roles do not carry over to table-level access, so everything in this lesson is how you secure the endpoint. In user identity mode, you cannot define RLS, CLS, or OLS directly using T-SQL.

Switching the mode changes what happens to the SQL security that already exists. The lesson on OneLake security enforcement covers the modes and the switch in detail.

Key takeaways

• CREATE USER cannot be run. Fabric creates the database user automatically when you run GRANT or DENY.
• DENY takes precedence, also over a permission that comes from a role, and a DENY on a schema beats a GRANT on one of its tables. REVOKE only removes a grant or a deny.
• SQL roles carry the permissions. CREATE ROLE creates the role, and ALTER ROLE with ADD MEMBER adds a member to it.
• Column-level security is GRANT SELECT with a column list, and a query that includes a column outside the list fails with a permission error.
• Row-level security is a schema-bound inline table-valued function plus CREATE SECURITY POLICY ... ADD FILTER PREDICATE ... WITH (STATE = ON).
• Security policies apply to all users, so a predicate must include the people who need every row.
• To change a predicate function, drop the security policy first, then alter the function and create the policy again.
• Direct Lake falls back to Direct Query mode when SQL row-level or column-level security protects the data.
• SQL security covers only SQL queries. For Spark and SQL together, use OneLake security roles with the endpoint in user identity mode.