How to implement Analysis Services Dynamic Security based on Microsoft CRM user access rights.

Introduction

In Analysis Services implementations, there is always a demand for implementing security. In this post, I’ll describe a dynamic or attribute security solution in Analysis Services that I built for a customer based on Microsoft CRM Salesperson Customer access rights. The data level or row level access is based on CRM security access rights and a SQL script. The SSIS package will deploy the User Active Directory account and Customer Number to a custom CRM table.  By leveraging the CRM security already established, we will reduce the administrative tasks needed for making additional security changes in Analysis Services whenever there is a CRM security change.

 

The Setup

Here are the pieces that we need to deploy to put the security in place:

1. Microsoft CRM table script
2. DataMart Acct_Access table script
3. DataMart User table script
4. Add Dynamics Security role to Cube and define MDX to filter Active Directory user access
5. Create SSIS package to populate and update Acct_Access and User tables in DataMart

 

1. Microsoft CRM table script 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[Acct_Access](

[AccountNumber] [varchar](200) NULL,

[AccountID] [varchar](200) NULL,

[DomainUser] [varchar](200) NULL

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING ON

GO

 

 

2. DataMart Acct_Access Table Script

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[Acct_Access](

[Access_Key] [bigint] IDENTITY(1,1) NOT NULL,

[AccountNumber] [varchar](200) NULL,

[AccountID] [varchar](200) NULL,

[DomainUser] [varchar](200) NULL,

[Customer_Key] [bigint] NULL,

[User_Key] [bigint] NULL,

[Company_Key] [bigint] NULL,

CONSTRAINT [PK_Access] PRIMARY KEY CLUSTERED

(

[Access_Key] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

 

3. DataMart User Table Script

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[User](

[User_Key] [bigint] IDENTITY(1,1) NOT NULL,

[DomainUser] [varchar](200) NULL,

CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED

(

[User_Key] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

 

4. Add Dynamic Security role to Cube and define MDX script to filter Active Directory User access.

Once this DataMart and CRM table structure are in place, your next step is to bring it into your Analysis Services solution.  Here are the steps to follow inside BIDS/SSDT to leverage these tables:

• Add the new tables (User and Acct_Access) to your Data Source View.
• Create a new dimension based on the User table.
• Hide all the columns by changing ‘AttributeHierarchyVisible’ to False. You don’t want Users to be able to view this dimension and process the size.
• Create a new ‘Measure Group’ in the cube based on the ‘Acct_Access’ table and name it, ‘Security.’
• Delete all Measures it creates except for the automatically generated Count Measure.
• Select the single Measure left to be hidden. You do this by selecting it and changing the Visible property to, ‘False.’ This will hide the entire ‘Measure Group’ from your Users, but you can still use it when writing MDX.
• Ensure these two have a Regular relationship setup in the Dimension Usage table of the Cube browser as seen in the screenshot below.
• In the Solution Explorer, right-click on Roles and select New Role.  Name the Role, ‘Dynamic Security’ and give it ‘Read Access’ in the General, Data Sources, and Cubes tabs.
• On the Membership tab, add your CRM groups or User accounts to this Role.
• On the Dimension Data tab, select the Customer dimension from the drop-down list.  Then select the Customer Number from the drop-down list.

• Inside the Dimension Data tab, select the Advanced tab. In the Allowed member set area enter this MDX:

EXISTS(

{[Customer].[Customer Number].Members},

STRTOSET(“[Customer].[Domain User].&[“+username()+”]”),

“Security”

)

 

5. Create SSIS package to populate and update ‘Acct_Access’ and User tables in DataMart.

With the table structure and the Cube solution complete, we need to create the SSIS package to populate and update the Acct_Access table in our CRM database and then bring the updated data into the DataMart.  You will need to:

1. First, we create a SQL script task and add it below to delete and repopulate the table with the current CRM Customer Access Data. Change the yellow highlighted areas below for your environment needs.

 

execute as login = ‘Contoso\bcrowell’

 

delete Acct_Access

GO

 

declare @DomainName varchar(200)

 

Make sure you execute the step to create the Domain User Table with the correct permissions.

 

 

execute as login = ‘Contoso\bcrowell’

if exists (select * from sysobjects where name = ‘DomainUser’)

drop table DomainUser

select  DomainName into DomainUser  from SystemUser

grant select on DomainUser to [Contoso\Domain Users]

 

 

declare  SEL_CUR cursor for select DomainName from SystemUser where IsDisabled = 0

open SEL_CUR

fetch next from SEL_CUR into @DomainName

while @@FETCH_STATUS = 0

BEGIN

SELECT SUSER_NAME(), USER_NAME();

execute as login = @DomainName

insert into Acct_Access(AccountNumber, AccountID, DomainUser) select AccountNumber, AccountID, @DomainName from FilteredAccount

fetch next from SEL_CUR into @DomainName

REVERT

END

close SEL_CUR

deallocate SEL_CUR

drop table DomainUser

 

GO

1. Add additional Data Flow task to take CRM Acct_Access data and populate/update the DataMart’s Acct_Access and User tables.

 

Conclusion

I’m not the first to deploy some of the techniques used above, and I started my learning by reviewing this blog post.

 

I hope this helps you with your future Analysis Services development projects.  Please contact KTL Solutions to help kick start your organization into becoming a data-driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM needs.

---

[avatar user=”bcrowell” size=”thumbnail” align=”left” /]BARRY CROWELL, MBA | BI/EDW Solution Architect

Barry is a Microsoft SQL Certified Professional with a strong knowledge of the Microsoft’s BI Stack (SSIS, SSRS, SSAS and Power BI). He has architected, developed and deployed clients’ Business Intelligence needs using Microsoft’s BI Stack and/or Solver’s BI360.  His solutions have included SSIS ETL tools, SSRS reports and dashboards, Excel dashboards, Power BI reports and dashboards, and SSAS cubes. He has performed implementations as the lead consultant and/or end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry, and in various industries such as housing authorities, universities, Tribal governments, and casino hospitality. He possesses a Bachelor’s Degree in Accounting and Business Administration from Black Hills State University and a Master’s in Business Administration from La Salle University.  Barry’s experience in both the IT consultant field and experience as an accountant gives him the ability to understand the issues from both the IT and Finance prospective, and provide a solution that fits the needs for all parties involved.