Monday, April 26, 2010

AS: Defining cube role using script

Syntax:
http://msdn.microsoft.com/en-us/library/ms133261%28v=SQL.100%29.aspx

Sample 1:
' Create the XML syntax to be used for the SetPermissions method
' of the Role object.
sDimensionSecurity = " "DefaultMember=""[Store].[Store Country].&[USA]"" " & _
"VisualTotalsLowestLevel=""[Store].[Store City]"">" & _
" "DeniedSet=""{[Store].[Store Country].&[Canada]," & _
"[Store].[Store Country].&[Mexico]}"" " & _
"Description=""USA Store Restriction""/>" & _
"
"

' The preceding XML syntax limits the users of the database role
' to viewing only stores in the USA, by denying read access to
' stores associated with the Mexico and Canada members of the
' [Store Country] level of the Stores dimension.

dsoRole.SetPermissions "Dimension:Store", sDimensionSecurity

Sample 2:
AddCubeRole "srvr", "BA for MBS Navision 400", "Sales", "Test Role", "domain\test"

Sub AddCubeRole(sServer, sDatabase, sCube, sRoleName, sUserList)
Dim dsoServer
Dim dsoDb
Dim dsoCube
Dim dsoRole
Dim sDimSecurity

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)
sDimSecurity = " " & _
" "UpperLevel=""[Principal Code].[Principal Code]"" " & _
"AllowedSet=""{[Principal Code].[Principal Code].&[01]}"" " & _
"/>" & _
"
"
dsoRole.UsersList = sUserList
dsoRole.SetPermissions "Dimension:Principal Code", sDimSecurity
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
---

Still don't know how to retrieve list of current permission.
Closest found so far:
Accessing Roles Permissions via DSO
ASP, DSO and Getting the UsersList from a role

No comments:

Post a Comment