DSO Script: Adding a Role to a Cube

<< An Excel Workbook that keeps the filters for all pivot tables synchronised | Home | SSIS alternative to the Slowly Changing Dimension transformation >>

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