DSO Script: Adding a Role to a Cube

I had a question over the weekend about how to add a role to a cube using DSO. As more and more people start using AS2k5 I think the interest is going to shift more towards AMO scripting, but someone else may find the following helpful.

The VBScript below shows the basic technique. The key thing to remember is that the Role object is set up at the database level. The cube's role collection basically holds references back to the database role collection (not that you can really tell this from the object model itself)

'----------------------------------------------------------------------------------------------
' Name       : AddRole.vbs
' Author     : Darren Gosbell (DPG)
' Date       : 13 June 2006
' Description: Demonstrates how to add cube roles
' Notes      : From a Command prompt type the following
'
'              cscript AddRole.vbs
'
'
' Revision History:
' Date          Who Ref#    Description
' 13/06/2006    DPG n/a     Initial version
'----------------------------------------------------------------------------------------------

AddCubeRole "SERVER", "Foodmart 2000", "HR", "Test Role", "SERVER\User1"
AddCubeRole "SERVER", "Foodmart 2000", "Sales", "Test Role 2", "SERVER\User1;SERVER\User2"


Sub AddCubeRole(sServer, sDatabase, sCube, sRoleName, sUserList)
 Dim dsoServer
 Dim dsoDb
 Dim dsoCube
 Dim dsoRole
 
 Set dsoServer = CreateObject("DSO.Server")
 dsoServer.Connect sServer
 Set dsoDb = dsoServer.MDStores(sDatabase)

 '// If a role with the same name exists - remove it
 If dsoDb.Roles.Find(sRoleName) Then
  dsoDb.Roles.Remove (sRoleName)
 End If
 Set dsoRole = dsoDb.Roles.AddNew(sRoleName, sbclsRegular)
 dsoRole.UsersList = sUserList
 dsoRole.Update
 
 Set dsoCube = dsoDb.MDStores(sCube)
 '// If a role with the same name exists - remove it
 If dsoCube.Roles.Find(sRoleName) Then
  dsoCube.Roles.Remove sRoleName
 End If
 dsoCube.Roles.AddNew sRoleName
 dsoCube.Update
 
 dsoServer.CloseServer
 
 Set dsoCube = Nothing
 Set dsoRole = Nothing
 Set dsoDb = Nothing
 Set dsoServer = Nothing
 
End Sub

Update: Dave (you know who you are) your email address is bouncing back so I hope you check back here to pick up this code :)

Print | posted on Tuesday, June 13, 2006 8:26 PM

Comments on this post

# re: DSO Script: Adding a Role to a Cube

Requesting Gravatar...
HI
I am working on SSAS project , could you please help me with roles in SSAS
I want to create the roles and give permissions dynamically from ascmd using XMLA
How to do that
waiting for a quick reply thanks
Left by Anky on Jul 17, 2007 6:07 AM
comments powered by Disqus