Security Reporting for SQL Server Analysis Services

Keeping up with an organization’s changing SQL Server Analysis Services (SSAS) security needs can be time consuming. I work for a hospital organization, so there’s a lot of focus on “appropriate use” and “need-to-know” data access. Our team is sometimes required to implement complex SSAS security definitions for a large number of cubes to meet users’ business needs. Reporting on who has access to certain data sets can be time consuming, and it’s sometimes difficult to communicate a complex security structure to leadership and data stewards. I developed an extraction and reporting system for our SSAS cubes (an SSAS security metadata cube) to reduce the effort needed to report on these security definitions.

One approach to keeping up with cube security changes is to delegate the ownership of who should and shouldn’t have access to a particular data set to the appropriate data owners (data stewards). This solution provides users with a tool they can use to audit the security for the data they own. With this approach, authorization for access can be confirmed by the appropriate data steward before the cube administrator is involved. The security metadata cube enables the data steward to quickly identify and analyze which users have access to the cubes, dimensions, and roles they’re responsible for. With this information easily available, the data steward can more efficiently communicate a security update to the cube administrator. Here, I’ll show you how to create the SSAS security metadata cube with minimal effort and time.

Extracting SSAS Security Metadata

There are many ways to create an SSAS security metadata cube to allow data stewards to audit cube security. I’ve chosen to use SQL Server Integration Services (SSIS) and the Script task to extract the necessary security metadata by leveraging the Analysis Management Objects (AMO) API. The AMO API is well documented and contains a roadmap for extracting security metadata from SSAS. To get started, you might want to download the SSIS project (Load_CubeSecurity_ISPackage) that loads the metadata security cube. (You can download this project by clicking the 103267.zip file at the top of the article page.) You’ll also need to create a table in a database to persist your cube metadata.

Figure 1 shows the high-level control flow of the SSIS package.

First, it extracts the AMO security definition using the Script task. Next, it deposits the records from the SSIS DataTable object created in the Script task into a table (AMOSecurityUDM.bAMOSecurityDefinition) using the Foreach loop container. Finally, we process the cube, which I’ll explain in more detail later. Let’s walk through the details of each control flow task.

Web Listing 1 provides the script used to extract the security metadata from AMO and pass the results back into a read/write SSIS variable, user::varDataTable. After defining the necessary objects and variables, the script creates a memory table with attributes to contain the security metadata. The script continues to do four nested loops to traverse the security portion of the AMO. Specifically, it’s looping through the AMO API Cube, Role, and Role Member security objects. At the innermost loop, the script adds a record to the DataTable variable. After walking through the security objects, the script writes the resulting data table to the user::varDataTable variable.

The Foreach loop container iterates over the records of the user::varDataTable memory object table populated from the Script task that’s shown in Figure 1. Each iteration of the Foreach loop container executes a single Execute SQL task. The script in Listing 1 deposits user::varDataTable records into a table one-by-one using an OLE DB connection. Now, let’s look at how to build the cube.

Creating the Security Metadata Cube

You can use the code in Web Listing 2 to create and publish the metadata security reporting cube, and the complete SSAS cube project, CubeSecurity_AnalysisServicesCube, is available for download in the 103267.zip file. You might want to have the project open as a reference as we outline the design details. You’ll need to update the data source connection settings for the project and the connection settings using the script in Web Listing 1.

To keep this process as simple as possible, the security metadata cube is sourced exclusively from the table (AMOSecurityUDM.bAMOSecurityDefinition) we populated in the SSIS package. All of the dimensions will be sourced from the fact table. You might choose to create independent dimension tables, but for the purpose of this article, we’ll use a single table to keep things easy. Figure 2 shows the data source view containing the single source table for the security cube.

I created attribute hierarchies from the columns in our AMOSecurityUDM.bAMOSecurityDefinition source table for Cube, CubeRoleSecurity, User, CubeAccessCode, and CubeRole. Each of these attributes represent objects in SSAS where security can be defined. I rolled these attribute hierarchies into two dimensions called UserAccess and AllowedCubeAccess. The UserAccess dimension will allow users of the security metadata cube to identify which cubeDatabase, Cube, Role, and roleMember they’re interested in for security reporting. The AllowedCubeAccess dimension is a simple yes/no qualification that states if a specific cube role has access to a particular cube. For example, a role called Testers might be created for a cube, but in production the Testers role might be disabled. The AllowedCubeAccess dimension has been hidden and its default member set to allowed to keep things as simple as possible for users of the security metadata cube. The AllowedCubeAccess dimension is a somewhat unnecessary dimension, but I included it because someone might want to know all the roles in a cube regardless of whether the roles are enabled.

Extending This Approach

This approach to reporting on SSAS security can be significantly expanded and modified based on your needs. The following are three additions you might consider for your security cube:

Audit Cube Security

Using a cube to report on the security metadata for SSAS has been very helpful for my organization. The security metadata cube is intuitive to navigate, and minimal time can be spent educating data owners about how to answer nearly all of their “who has access to that” questions, giving you more time to develop and implement data mart cubes.

Author’s Note: I’d like to thank Clarke Morris for his VBScript assistance and for creating the code in Listing 2 to extract Active Directory group information.

Web Listing 1: Code to Extract Security Metadata from AMO

'Add a  reference to Microsoft.AnalysisServices'May need to copy the “Microsoft.AnalysisServices.DLL”  into your SDKAssemblies folderImports SystemImports System.DataImports AMO = Microsoft.AnalysisServicesPublic Class ScriptMainPublic Sub Main()      Dim amoServer As New AMO.Server    Dim amoDatabase As New AMO.Database    Dim amoRole As New AMO.Role    Dim amoRoleMember As New AMO.RoleMember    Dim amoCube As New AMO.Cube    Dim amoCubePermission As New AMO.CubePermission    Dim amoCubeDimensionPermissionCollection As AMO.CubeDimensionPermissionCollection    Dim amoCubeDimensionPermission As New AMO.CubeDimensionPermission    Dim DimensionReadPermission As String    Dim tblDataTable As Data.DataTable      'create datatable    tblDataTable = New DataTable("AmoSecurityTable")    'create attributes for mapping security metadata    tblDataTable.Columns.Add(New DataColumn("Role", GetType(String)))    tblDataTable.Columns.Add(New DataColumn("CubeDatabase", GetType(String)))    tblDataTable.Columns.Add(New DataColumn("Cube", GetType(String)))    tblDataTable.Columns.Add(New DataColumn("CubeAccessCode", GetType(String)))    tblDataTable.Columns.Add(New DataColumn("Rolemember", GetType(String)))    '''''''''''''''''    'Establish a connection to the Analysis Server.     '**** FILL IN > and >. The 'Adventureworks DW' catalog works well. The purpose is to simply establish a connection to the server.     amoServer.Connect("Data Source=>;Initial Catalog=>;Provider=MSOLAP.3;Integrated Security=SSPI;Impersonation Level=Impersonate;")      For Each amoDatabase In amoServer.Databases 'traverse Analysis Services databases         For Each amoRole In amoDatabase.Roles   'traverse Analysis Services database roles        For Each amoCube In amoDatabase.Cubes 'traverse Analysis Services cubes            For Each amoRoleMember In amoRole.Members  'traverse Analysis Services RoleMembers            Try            'Create DataRow based on tblDataTable Object                Dim dr As DataRow = tblDataTable.NewRow()            'Assign AMO security values to DataRow attributes                dr("Role") = amoRole.Name                dr("CubeDatabase") = amoDatabase.Name                dr("Cube") = amoCube.Name                dr("CubeAccessCode") = amoCube.CubePermissions.GetByRole(amoRole.ID).Read.ToString()                dr("RoleMember") = amoRoleMember.Name            'Add DataRow row to tblDataTable                 tblDataTable.Rows.Add(dr)            Catch ex As Exception            End Try          Next 'traverse Analysis Services RoleMembers        Next 'traverse Analysis Services cubes        Next 'traverse Analysis Services database roles    Next 'traverse Analysis Services databases     ' Assign memory table to Integration services variable "user::varDataTable"    Dts.Variables("varDataTable").Value = tblDataTable    'Disconnect from server    amoServer.Disconnect()    'report task success    Dts.TaskResult = Dts.Results.SuccessEnd SubEnd Class

Listing 1: Code to Place user::varDataTable Records into a Table

DECLARE @role as varchar(50)DECLARE @cubeDatabase as varchar(50)DECLARE @cube as varchar(50)DECLARE @cubeAccessCode as varchar(50)DECLARE @roleMember as varchar(50)SET @role = ?SET @cubeDatabase = ?SET @cube = ?SET @cubeAccessCode = ?SET @roleMember = ?insert AMOSecurityUDM.bAMOSecurityDefinition(role,CubeDatabase,cube,CubeAccessCode,roleMember)values(@role,@cubeDatabase,@cube,@cubeAccessCode,@roleMember)

Web Listing 2: Code to Create the Security Metadata Table

--- create list of local groups and insert into tabletruncate table AMOSecurityUDM.localgroupmemberscreate table #localmembers(members varchar(300))create table #localgroups([group] varchar(300), comment varchar(300))declare @enumstring varchar(300)create table #localmembers2c([Localgroup] [varchar](200) NULL,        [Members] [varchar](200) NULL)set @enumstring = 'execute xp_enumgroups '>''select @enumstringinsert into #localgroups([group],comment) exec(@enumstring)-----------------------------------------------------------------------------------------------------run each group name through net localgroup command –insert unfiltered data into tabledeclare @group varchar(100)declare localgroup cursor for select [group] from #localgroupsopen localgroupfetch next from localgroup into @groupwhile @@fetch_status = '0'BEGINdeclare @netcommand varchar(300)declare @xpcommand varchar(300)set @netcommand = 'net localgroup ' + @group + 'select @netcommand set @xpcommand = 'xp_cmdshell ''+ @netcommand + ''select @xpcommandinsert into #localmembers(members)exec(@xpcommand)declare @membersc varchar(200)declare cursor_column cursor forselect members from #localmembers 

Listing 2: xp_enumgroups Stored Procedure Used to Extract Local Group Membership

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [AMOSecurityUDM].[bAMOSecurityDefinition]([role] [varchar](50) NULL,[CubeDatabase] [varchar](50) NULL,[Cube] [varchar](50) NULL,[CubeAccessCode] [varchar](50) NULL,[RoleMember] [varchar](50) NULL)