During one of my latest projects there was a business requirement to allow power user to manage cube authorization. The list of authorized users was rapidly changing during a day so it had to automatically affect cube authorization.
Giving a power user SQL Server Management Studio is not a good way to solve this problem :)
That is why the decision was obvious – implementing AMO (Analysis Management Objects) to manage cube security.
I have decided to use AMO in SSIS package using Script Task. As you can see control flow contains two components.
1. Script Task responsible for clearing SSAS role members
2. Data Flow Task which adds members to a SSAS role.
Below picture presents the control flow:
Clear Role Members
More about “Analysis Management Objects (AMO)” library:
http://msdn.microsoft.com/en-us/library/ms124924.aspx
1: Imports System
2: Imports System.Data
3: Imports System.Math
4: Imports Microsoft.SqlServer.Dts.Runtime
5: Imports Microsoft.AnalysisServices '<- AMO Library
6:
7: Public Class ScriptMain
8:
9: Public Sub Main()
10:
11: Dim connectionString As String
12: Dim dbName As String
13: Dim roleName As String
14:
15: Dim srv As New Server()
16: Dim db As New Database()
17: Dim role As New Role()
18:
19: connectionString = Dts.Variables("connectionString").Value.ToString()
20: dbName = Dts.Variables("dbName").Value.ToString()
21: roleName = Dts.Variables("roleName").Value.ToString()
22:
23:
24: srv.Connect(connectionString)
25:
26: db = srv.Databases.FindByName(dbName)
27: role = db.Roles.FindByName(roleName)
28:
29: If Not role Is Nothing Then
30: role.Members.Clear()
31: End If
32:
33: role.Update()
34:
35: If srv.Connected Then
36: srv.Disconnect()
37: End If
38:
39: Dts.TaskResult = Dts.Results.Success
40: End Sub
41:
42: End Class
Power user was able to manage list of authorized users through an excel configuration file. Below picture presents the fragment of the Data Flow Task “Role Members”.
Let see the Add Role Member Script
1: Imports System
2: Imports System.Data
3: Imports System.Math
4: Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
5: Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
6: Imports Microsoft.AnalysisServices ' <- AMO Library
7:
8:
9: Public Class ScriptMain
10: Inherits UserComponent
11:
12: Dim cnt As Integer
13:
14: Public Overrides Sub CreateNewOutputRows()
15: MyBase.CreateNewOutputRows()
16:
17: End Sub
18:
19: Public Overrides Sub InputDirection_ProcessInputRow(ByVal Row As InputDirectionBuffer)
20:
21: Dim srv As New Server()
22: Dim db As New Database()
23:
24: Dim role As New Role()
25: Dim roleMember As New RoleMember()
26: Dim dbPermission As New DatabasePermission()
27: Dim cubePermission As New CubePermission()
28:
29: srv.Connect(Variables.connectionString)
30: db = srv.Databases.FindByName(Variables.dbName)
31: role = db.Roles.FindByName(Variables.roleName)
32:
33: roleMember = New RoleMember(Row.ACTIVEDIRECTORY)
34:
35: Try
36:
37: If Not role.Members.Contains(roleMember) Then
38: role.Members.Add(roleMember)
39: role.Update()
40: End If
41:
42: Finally
43:
44: If srv.Connected Then
45: srv.Disconnect()
46: End If
47:
48: End Try
49:
50: End Sub
51:
52: End Class
I hope, this blog will be useful.
Subscribe