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
 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
 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