Introduction

The Analysis Services (SSAS) tabular model does provide security. The first line is integration with Windows logins or ActiveDirectory accounts. There is no SQL login like that used in the database engine. ActiveDirectry (AD) groups can be used to organize the permission from a higher level. SSAS tabular can go a level further with table filter access. Role based permissions can use DAX to limit data from that table and related tables. This truly gives enterprises everything they need for a single source of data and limited access.

Security

SSAS tabular uses Windows logins (or AD accounts) for access to the model data. SQL Server Data Tools (SSDT) provides a Security area for these additions while editing a model. Figure 1 shows the Role Manager screen.

Figure 1 Role Manager

To get to this screen, you have to select the Model menu choice and then Roles… In the new Visual Studio 2019, this is from the Extensions main menu like Figure 2. In versions of SQL Server Data Tools, the Model menu choice is on the main menu of the Visual Studio shell. You can also right-click Roles in the Solution Explorer window and select Roles.

NOTE: The Visual Studio 2019 release changed the way Analysis Services, Integration Services and Reporting Services development is installed. You can only install it through Extensions. In 2017, you could have installed from extensions with Visual Studio or with the SQL Server Data Tools installation.

Figure 2 Extensions Menu Choice for Model menu

The main permissions for a Role are shown in Figure 3. None, Administrator, and Read permissions are self-explanatory. Process is the ability to ‘process’ a tabular model to refresh the data in it. I guess there are some DevOps positions that might need to process new data but not be able to Read data. This Process permission can be given to a service account and processing can be automated.

Figure 3 Role Permissions

Once the Role(s) are created, the user can add the logins related to that role. Figure 4 shows the Read role and the Members tab selected below it. Here, one or more accounts can be related to the Read Role. Without a Role like this, the only way someone could view the model is to be an Administrator of the SSAS service.

Figure 4 Add Members to Role

NOTE: There is a new button in 2019 for Add External… that prompts for an email address. This is for Azure AD email in a company Azure tenant.

Role Data Level Security

SSAS tabular provides Row Filters assigned to a Role. This enables to filter data at a granular level based on a DAX filter. Figure 5 shows the Row Filters tab for the ClothingSupplier Role. One or more DAX filters can be applied to a Role.

Figure 6 Clothing Supplier Role

The Role in Figure 6 has a DAX Filter for the Supplier table. The [Category] reference in the DAX Filter is the attribute or column from the Supplier table. This row filter is for “Clothing Supplier” as the category. This filter will persist throughout the data in the tabular model based on relationships. Any data from the Transactions table will be filtered for this Role through the relationship to Supplier.

A nice feature in the model is to use a Role to Analyze in Excel. Figure 7 shows the prompt that allows a selection of the Role. By selecting ClothingSupplier, the connection string in Excel will include the Roles parameter even though my AD account is not associated with that Role.

Figure 7 Analyze in Excel Role Prompt

Figure 8 shows the data in an Excel Pivot Table with the Category from Supplier table filtering the data as just ‘Clothing Supplier’. The figure also shows the connection used for the Excel Pivot Table and the Properties… button to edit the string. The Roles=ClothingSupplier is added to the connection string.

Figure 8 Analyze in Excel Filtered by Clothing Supplier

If you remove the Role=ClothingSupplier out of the connection string, all Category(s) will be shown like Figure 9. The permissions return to the AD or Windows login.

Figure 9 No Role in Connection String

Some people get alarmed from this connection string Roles parameter and the ability to remove it. But, this is just for testing. The real permissions are based on AD or Windows logins. In the real world, it does not matter if the Roles entry is not in the connection string.

NOTE: Some might have noticed that in Figure 6 there was a tab for Tables and Columns. There is a new feature in 2019 – object level security. Those are for another article in this series.

Summary

Security is big deal for everyone. SSAS is no different. It is better to not leave it wide-open for all to see. Roles enable AD or Windows logins to have specific permissions to the model. The DAX Filter allows more granular filters at a row or aggregate level. The combination of both provides what most enterprises need with an Analytical database.