In a data warehouse environment based on SQL Server, SSIS, SSAS MD and SSRS, a common request is to limit user permissions to specific data and measures. It’s common end users have only access to specific cost centres and/or specific (sensitive) measures. To limit the administrative work in managing SSAS roles and SSRS permissions, I have developed an automated mechanism where users automatically have access to the data and reports they are allowed to see. The permissions can be managed by configuring two master tables which can be administrated by any users, no specific technical knowledge is required.
Prerequisite
- Access to Active Directory
- Employeenumbers are stored in Active Directory and correspond with Employeenumber in HR system.
Arhitecture
Get data from Active Directory
You can get data from the Active Directory (On-Premise) by setting up a linked server in SQL Server. Pinal Dave wrote a great article on how to setup a linked server in SQL Server.
Make sure the account to be used has read access on the Active Directory
When you have successfully setup the linked server you are able to query the Active Directory with T-SQL. Also you should see your linked server within SQL Server Management Studio in the folder “Server Objects\Linked Servers\
CREATE TABLE Users (
sAMAccountName VARCHAR(255) NULL,
cn VARCHAR(255) NULL,
givenName VARCHAR(255) NULL,
sn VARCHAR(255) NULL,
EmployeeNumber VARCHAR(255) NULL
)
I used the following query to get the data from the Active Directory linked server:
DECLARE @Query NVARCHAR(1024)
DECLARE @Path NVARCHAR(1024)
SET @Query = '
SELECT sAMAccountName, cn, givenName, sn, EmployeeNumber
FROM OPENQUERY(ADSI,
''SELECT sAMAccountName, cn, givenName, sn, EmployeeNumber
FROM ''''LDAP://DC=<Domain Name>,DC=<Domain Name>''''
WHERE
objectClass = ''''user''''
AND
objectCategory = ''''person''''
AND
''''userAccountControl:1.2.840.113556.1.4.803:''''<>2'')
WHERE
1=1
AND (NULLIF(EmployeeNumber,'''') IS NOT NULL
AND sn IS NOT NULL)
ORDER BY 1 '
EXEC SP_EXECUTESQL @Query
WITH RESULT SETS (
(
sAMAccountName VARCHAR(255) NULL,
cn VARCHAR(255) NULL,
givenName VARCHAR(255) NULL,
sn VARCHAR(255) NULL,
EmployeeNumber VARCHAR(255) NULL
)
)
Replace
The “Add domain name to account” in the SSIS package is a derived expression to add the mandatory domain name to the account name. We will need this later in the process where we will dynamically generate the SSAS roles by XMLA and add the AD user accounts.
Create the table to load employee data from you HR system into:
CREATE TABLE [Employees](
[EmployeeNumber] [varchar](100) NOT NULL,
[EmployeeName] [varchar](100) NOT NULL,
[JobTitleCode] [varchar](100) NOT NULL,
[JobTitle] [varchar](100) NOT NULL
) ON [PRIMARY]
This scenario is depending on the HR system in use, make sure to get the EmployeeNumber, Employeename, JobTitle and JobTitleCode and load the data into a SQL table.
Get data from Master table
For this scenario you need two tables, one table containing the job title code and the permission level. The other table is a detail table for the job titles which have limited permissions based on one or more cost centres.
Create your master tables to store your permissions per JobTitleCode.
CREATE TABLE [PermissionLevel](
[JobTitleCode] [varchar](100) NOT NULL,
[Level] [varchar](100) NOT NULL
) ON [PRIMARY]
CREATE TABLE [LimitedPermissions](
[JobTitleCode] [varchar](100) NOT NULL,
[CostCentreCode] [varchar](100) NOT NULL
) ON [PRIMARY]
The data can be stored in a format which suits you best. You can store it in a CSV file or a SQL table.
Just make sure you can store:
- Job-title-code
- Permission level (full or limited)
- Cost centres for JobTitles who have limited permission level
Now you should have the following data available in your table in SQL Server:
- A table containing data from your Active Directory, accountname + employeenumber
- A table containing HR data, employeenumber + job-title-code + job-title
- Two tables containing permissions
SELECT
E.EmployeeNumber
,E.EmployeeName
,E.JobTitleCode
,E.JobTitle
,U.sAMAccountName
,PL.[Level]
,LP.CostCentreCode
FROM Users U
JOIN Employees E
ON U.EmployeeNumber = E.EmployeeNumber
JOIN PermissionLevel PL
ON E.JobTitleCode = PL.JobTitleCode
LEFT JOIN LimitedPermissions LP
ON LP.JobTitleCode = PL.JobTitleCode
Example output:
To generate the SSAS roles in the end we need to add one more thin, the string representation of the Security Identifier for each Active Directory account. To get the string representation of the Active Directory account for each employee I created a custom scalar-valued functions called “fnSIDToSTring”. The function will take the binary representation of an SID and will return the string representation of the SID. SQL Server has a native function called SUSER_SID which returns the binary representation of the SID and accepts the AD accountname as input.
CREATE FUNCTION [AD].[fnSIDToString]
(
@BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN
IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)
DECLARE @StringSID VARCHAR(100)
DECLARE @i AS INT
DECLARE @j AS INT
SELECT @StringSID = 'S-'
+ CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
SELECT @StringSID = @StringSID + '-'
+ CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))
SET @j = 9
SET @i = LEN(@BinSID)
WHILE @j < @i
BEGIN
DECLARE @val BINARY(4)
SELECT @val = SUBSTRING(@BinSID, @j, 4)
SELECT @StringSID = @StringSID + '-'
+ CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))
SET @j = @j + 4
END
RETURN ( @StringSID )
END
At this point in time we can load the collected data into our star schema. In your star schema you need a new dimension DimUser, DimCostCentre (I assume you already have one) and a new fact table FactPermissions.
Create the Dimuser and FactPermissions tables:
CREATE TABLE [dbo].[DimUser](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Accountname] [varchar](255) NOT NULL,
[Name] [varchar](500) NOT NULL,
[EmployeeNumber] [varchar](500) NOT NULL,
[SID] [varchar](255) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[FactPermissions](
[FactPermissionsID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[CostCentreID] [int] NULL,
[Level] [varchar](100) NULL
) ON [PRIMARY]
/*
Create statement for DimCostCentre if you don’t have this dimension
CREATE TABLE [dbo].[DimCostCentre](
[CostCentreID] [int] NOT NULL,
[CostCentreCode] [varchar](100) NOT NULL,
[CostCentreName] [varchar](100) NULL
) ON [PRIMARY]
*/
Combine and load your data into your star a new fact table called: “FactPermissions” and link your fact table to your cost centre and user dimension.
Example output for FactPermissions:
Example output for DimUser:
Example output for DimCostCentre:
Change SSAS cubes for dynamic security and add two SSAS roles
To make SSAS filter fact data for users who have limited permissions based on cost centres we need to make some slight changes to the cube. Devin Knight wrote a great article about setting up dynamic security which I used in my scenario.
In this scenario the “FactPermissions” table is the “UserStateBridge” and the “DimCostCentre” table is the “DimState” table. You can filter the facts in the datasourceview of your cube to only load facts which are related to the limited permissions, WHERE Level = “”Limited”.
Add first SSAS role LimitedPermissions
Add the first SSAS role with limited permissions as instructed in the article of Kevin, also don’t forget to set to set the security on the Dimension Data tab.
Enter the following MDX in the “Allowed member set” box:
NonEmpty ([DimCostCentre].[CostCentre ID].Members,
(StrToMember("[DimUser].[Accountname].[" + UserName () + "]"),
[Measures].[Users With Limited Permissions Count]))
After creating the SSAS role with limited permissions, save it and give it the name “LimitedPermissions”
Add second SSAS role LimitedPermissions
Now we have changed our SSAS cube(s) to work with dynamic dimension security and we have aa SSAS role for limited permissions. We need to add one more SSAS role for users who will have FULL permissions. Just create another SSAS role within the SSAS database and grant the role READ permissions to the cube(s). You can call the SSAS role FullPermissions.
By now you should see two SSAS roles in your SSAS database:
Assign AD users to SSAS roles
In this stadium we should have the following situation:
- Data and permissions loaded into your star schema containing the following tables
- DimUser, DimCostCentre and FactPermissions
- SSAS cubes which can work with dynamic dimension security based on these tables
- Two SSAS roles: LimitedPermissions and FullPermissions.
After successfully setup a linked server you should see your linked server within SQL Server Management Studio in the folder “Server Objects\Linked Servers\
Stored procedure [AddUserWithLimitedPermissions]
The first stored procedure will add members to the LimitedPermissions SSAS role, the stored procedure will accept the databasename of SSAS and the name of the SSAS role.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [AddUserWithLimitedPermissions](@DatabaseID varchar(255), @RoleID varchar(255)) AS
DECLARE @Account VARCHAR(255)
DECLARE @SID VARCHAR(255)
DECLARE @XMLA AS VARCHAR(MAX)
DECLARE @BatchXMLA_start AS VARCHAR(MAX) = '
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>' + @DatabaseID + '</DatabaseID>
<RoleID>' + @RoleID + '</RoleID>
</Object>
<ObjectDefinition>
<Role xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
<ID>' + @RoleID + '</ID>
<Name>' + @RoleID + '</Name>
<Members>
'
DECLARE @BatchXMLA_end AS VARCHAR(MAX) = '
</Members>
</Role>
</ObjectDefinition>
</Alter>
</Batch>
'
SET @XMLA = @BatchXMLA_start
DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT DISTINCT
DU.[Accountname], DU.[SID]
FROM
FactPermissions FP
JOIN
DimUser DU
ON FP.UserID = DU.UserID
WHERE FP.[Level] = "Limited"
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @Account, @SID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @XMLA = @XMLA +
'<Member>
<Name>' + @Account + '</Name>
<Sid>' + @SID + '</Sid>
</Member>'
FETCH NEXT FROM MY_CURSOR INTO @Account, @SID
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
SET @XMLA = @XMLA + @BatchXMLA_end
EXEC (@XMLA) At SSAS
GO
Stored procedure [AddUserWithFullPermissions]
The second stored procedure will add members to the SSAS role FullPermissions.
CREATE PROCEDURE [AddUserWithFullPermissions](@DatabaseID varchar(255), @RoleID varchar(255)) AS
DECLARE @Account VARCHAR(255)
DECLARE @SID VARCHAR(255)
DECLARE @XMLA AS VARCHAR(MAX)
DECLARE @BatchXMLA_start AS VARCHAR(MAX) = '
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>' + @DatabaseID + '</DatabaseID>
<RoleID>' + @RoleID + '</RoleID>
</Object>
<ObjectDefinition>
<Role xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
<ID>' + @RoleID + '</ID>
<Name>' + @RoleID + '</Name>
<Members>
'
DECLARE @BatchXMLA_end AS VARCHAR(MAX) = '
</Members>
</Role>
</ObjectDefinition>
</Alter>
</Batch>
'
SET @XMLA = @BatchXMLA_start
DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT DISTINCT
DU.[Accountname], DU.[SID]
FROM
FactPermissions FP
JOIN
DimUser DU
ON FP.UserID = DU.UserID
WHERE FP.[Level] = "Full"
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @Account, @SID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @XMLA = @XMLA +
'<Member>
<Name>' + @Account + '</Name>
<Sid>' + @SID + '</Sid>
</Member>'
FETCH NEXT FROM MY_CURSOR INTO @Account, @SID
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
SET @XMLA = @XMLA + @BatchXMLA_end
EXEC (@XMLA) At SSAS
You can call the stored procedures from an SSIS package to update the members of your SSAS role at a point in time when it suits you best. In my case I update the members after a successful refresh of the data warehouse.
EXEC [AddUserWithFullPermissions] @DatabaseID = N'MySSASDatabase', @RoleID = N'FullPermissions'
EXEC [AddUserWithLimitedPermissions] @DatabaseID = N'MySSASDatabase', @RoleID = N'LimitedPermissions'
If you check the membership of the SSAS role you will find the AD accounts in your DimUser table combined with your fact table FactPermissions are mapped to the corresponding LimitedPermissions and/or FullPermissions SSAS role.
FullPermissions role:
LimitedPermissions role:
The final result
When you browse the cube you can choose to browse the cube using a different username.
Right-click your cube, click browse, click the user icon in the top left and choose: “other user”.
Change the username to one of the users with full or limited permissions.
Drag your measures and cost centre on the grid and you will see data if filtered by cost centre for users with limited permissions. Users with full permissions will see all data.
I hope this article will help you on how to setup dynamic dimension security with SSAS MD and how you can dynamically add AD usernames to SSAS roles.