Greetings,

I am looking for some direction here, and potentially a little sample
code. I am needing the ability to create roles with dimensional
security inside of a cube. I am needing the most efficient way to do
this (believe XMLA). But my issue is if I try to script out an
existing cube role in SSAS 2005, it only gives me the "shell" of the
information - I don't see a way to have it include the excluded /
filtered sets inside of the XMLA Definition.

Can anyone lend a hand in hand in where to find how to get all of the
specifics. I see if I browse the cube definition file, there is a
section for <CubePermissions> Is there a way to wrap that into an XLMA
script?

Looking for some direction here!

Thanks!

This is one example of setting up the cube permission xmla script:

<Create AllowOverwrite="true"
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>Adventure Works DW</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
</ParentObject>
<ObjectDefinition>
<CubePermission
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ID>CubePermission</ID>
<Name>CubePermission</Name>
<RoleID>Role</RoleID>
<Read>Allowed</Read>
<ReadSourceData>None</ReadSourceData>
<CellPermissions>
<CellPermission>
<Access>Read</Access>
<Expression>Not ([Employee].[Employees].CurrentMember
IS [Employee].[Employees].&[112])</Expression>
</CellPermission>
</CellPermissions>
</CubePermission>
</ObjectDefinition>
</Create>

dharper (AT) quilogy (DOT) com wrote:

One way to capture the XMLA would be to set up your project in BIDS to
only deploy changes. Then start up SQL Profiler and set it to profile
Analysis Services. Once you have done that, change or add a role and
deploy the project. You will then be able to see the XMLA commands that
BIDS sends to the server.

Another approach, if you would prefer to work with .Net code as opposed
to working with raw XMLA would be to use the AMO library to create your
roles.

The following is a snippet from the AMOAdventureWorks that is part of
the samples that come with SQL Server.

//====================================

Cube cube = db.Cubes[0];
Role role;
DatabasePermission dbperm;
CubePermission cubeperm;

// Create the Readers role.
role = db.Roles.Add("Readers");
role.Members.Add(new RoleMember("")); // e.g. redmond\johndoe
role.Update();

// Assign Read permissions to this role.
// Members of this role can discover and query the Adventure Works cube.
// However the Customer dimension is restricted to the United States.
dbperm = db.DatabasePermissions.Add(role.ID);
dbperm.Read = ReadAccess.Allowed;
dbperm.Update();

cubeperm = cube.CubePermissions.Add(role.ID);
cubeperm.Read = ReadAccess.Allowed;
Dimension dim = db.Dimensions.GetByName("Customer");
DimensionAttribute attr = dim.Attributes.GetByName("Country-Region");
CubeDimensionPermission cubedimperm = cubeperm.DimensionPermissions.Add
(dim.ID);
cubedimperm.Read = ReadAccess.Allowed;
AttributePermission attrperm = cubedimperm.AttributePermissions.Add
(attr.ID);
attrperm.AllowedSet = "{[Customer].[Country-Region].[Country-Region].&
[United States]}";
cubeperm.Update();

//====================================


--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1158785916.725216.20710 (AT) k70g2000cwa (DOT) googlegroups.com>,
iloveseahawks (AT) gmail (DOT) com says...

Thanks for the great start....would anyone happen to have the VB.NET
code vs. the c# example for AMO...I am getting close, but not clear on
how to bind the role to the AttributePermisions

Thanks in advance!

Dave

Darren Gosbell wrote:

That's not hard. I'm multi-lingual You just need to be careful of the
wrapping.

'//======================================

Dim cube As Cube = db.Cubes(0)
Dim role As Role
Dim dbperm As DatabasePermission
Dim cubeperm As CubePermission

' Create the Readers role.
role = db.Roles.Add("Readers")
role.Members.Add(New RoleMember("")) ' e.g. redmond\johndoe
role.Update()
' Assign Read permissions to this role.
' Members of this role can discover and query the Adventure Works cube.
' However the Customer dimension is restricted to the United States.
dbperm = db.DatabasePermissions.Add(role.ID)
dbperm.Read = ReadAccess.Allowed
dbperm.Update()
cubeperm = cube.CubePermissions.Add(role.ID)
cubeperm.Read = ReadAccess.Allowed
Dim objDim As Dimension = db.Dimensions.GetByName("Customer")

Dim attr As DimensionAttribute = objDim.Attributes.GetByName("Country-
Region")

Dim cubedimperm As CubeDimensionPermission =
cubeperm.DimensionPermissions.Add(objDim.ID)

cubedimperm.Read = ReadAccess.Allowed

Dim attrperm As AttributePermission =
cubedimperm.AttributePermissions.Add(attr.ID)

attrperm.AllowedSet = "{[Customer].[Country-Region].[Country-Region].&
[United States]}"

cubeperm.Update()

'\\ =========================

PS. sites like the following:

http://authors.aspalliance.com/aldot...translate.aspx
http://www.carlosag.net/Tools/CodeTr...r/Default.aspx

....are great for converting small code snippets from C# to VB.NET

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1158875383.801304.151460 (AT) d34g2000cwd (DOT) googlegroups.com>,
dharper (AT) quilogy (DOT) com says...