In this 30-minute video, part of our Cube Design series, Chris Webb explains the SQL Server Analysis Services security. SSAS security is role-based, and all authentication is performed via Windows authentication—it is not possible to create usernames and passwords in SSAS. Users can be members of multiple roles, and in that case a user will have the accumulated permissions from all of the roles they are members of.

There are two types of SSAS security: administrative security and data security. Administrative security can be set at the server level and the database level, and it controls the ability to do things like: create or delete objects, process, and script to XMLA.

Data security controls what data users can see when they browse the cube. Data security can be set at three levels: at the cube level, at the dimension hierarchy level, and at the level of the individual cell. At the cube level, users can either query a cube or not. Dimension security, the most commonly-used type of security, controls which members on a dimension hierarchy a user can see. It is set up on the Dimension Data tab in the Role Editor in Visual Studio. Once you have done that, you can test the role in the Cube Browser tab by pressing the Change User button, or by using the EffectiveUserName or Roles connection string properties. Cell security controls whether users can see the values in individual cells—however, it is rarely used because it is hard to set up and can have a big impact on query performance. In some cases it may be possible to replace cell security with dimension security by remodelling your cube.

Dynamic security is the ability of a single role to return different permissions for different users. This is possible by using an MDX expression within the role that uses either the Username or Customdata functions.

Make sure to watch the remaining modules in this course to learn how to build cubes using SSAS.