SSAS Tabular Part 11 : Apply Dynamic row level security based on windows user

Dynamic security provides row-level security based on the windows user name or login id of the user currently logged on.

To implement dynamic security, we must add a table to our model containing the Windows user and different dimension table primary key.

For example, suppose we have a dimension table named Country. This table contain 2 columns named Country_Id and Country_Name.

To implement dynamic security, we create a table named UserPermission. This table contain 2 columns named User_Name and Dim_Id.

Here we can see 3 record exists in Country table. Next table UserPermission  contain 3 different windows username with Dim_Id. Here it means that NT-RPS\Icchasoo user has permission for country (India, United Kingdom), NT-RPS\UtkarshMis user has permission for country (India) and NT-RPS\AnimeshTha user has permission for country (United States)

Steps to do:

1. Create a tabular Project by SQL Server Data Tools.
2. Create a database connection.
3. Now click Select from a list of tables and views to choose the data to import selected, and then click Next.
4. On the Select Tables and Views page, select 2 table Country and UserPermission and click Finish. (Now we can see both table inside Grid view.
5. Go to Model and click on Roles.
6. Create a new role and give name as PermittedUserRole
7. Under Row filter TAB we can see both Country and UserPermission table. Now we have to write DAX query in DAX Filter 

           Country             --> =CONTAINS(User_Permission,User_ Permission                                                                                                                                  [User_Name],USERNAME(),User_ Permission [Dim_Id],

                                                                     Dim_Country[Country_Id

           
           UserPermission --> =FALSE()

    (We apply =FALSE() because UserPermission's data  should not show to any user)

8. Click on Members TAB and import all the username which exists in our User_                        Permission table.

  9. Deploy this solution.

10. Open excel and connect the particular SSAS Tabular Database with NT-RPS\Icchasoo          username and password.

11. We will see the permitted Country for the NT-RPS\Icchasoo user.