基于Windows登录的Analysis Services角色配置问题

Great question! This is exactly the scenario where dynamic row-level security (RLS) shines—you can avoid creating duplicate roles entirely by leveraging your team's existing Windows domain credentials to automatically filter Cube data per user. Here's a step-by-step breakdown tailored to your needs:

1. Build a Single Dynamic Role (No More Duplicates!) #

Instead of creating 10 identical roles, you only need one role with a dynamic filter tied to the current user's Windows identity:

For SSAS Tabular Cubes #

In the role's row-level security settings, add a filter expression for your sales table/dimension that matches the user's Windows account to their sales data:

[SalesPersonADAccount] = USERNAME()

Replace [SalesPersonADAccount] with the actual column in your Cube that stores the full Windows credential (e.g., domain\amartin).

For SSAS Multidimensional Cubes #

Navigate to the role's Dimension Data permissions for your Sales Person dimension. Use an MDX expression to filter members based on the current user:

[Sales Person].[AD Account].CurrentMember.Name = USERNAME()

This ensures only the sales records linked to the logged-in user's Windows account are visible.

2. Align Cube Data with Windows Credentials #

Make sure your Cube's sales dimension includes a column that stores each salesperson's full Windows domain account (exact match to their login, like domain\amartin). If this field doesn't exist yet:

• Add the account field to your underlying sales database (you can sync this from Active Directory if needed)
• Refresh your Cube to include this new dimension attribute

3. Validate the Setup #

• Log in as Anne MARTIN (domain\amartin) and confirm you only see her sales data
• Test with another user (like Patrick DUMONT) to verify they can't access Anne's records
• Double-check that the USERNAME() function returns the exact format stored in your Cube (e.g., with/without the domain prefix—adjust the expression if needed, e.g., use RIGHT(USERNAME(), LEN(USERNAME()) - CHARINDEX('\', USERNAME())) to extract just the username if your Cube stores that)

4. Automate New Hire Onboarding #

When a new salesperson joins:

1. Add their Windows domain account to your sales database's corresponding field
2. Run an incremental refresh on your Cube to pull in the new user's data
3. They’ll automatically inherit the dynamic role's permissions—no need to create or configure any new roles!

This approach keeps your security setup clean, scalable, and fully tied to your existing Windows authentication system.

内容的提问来源于stack exchange，提问作者Damien K.