Role deleted

I get this error when I try to build my cube.

Error 1 The 'Role' with 'ID' = 'Sales_Marketing' doesn't exist in the collection. 0 0

I had added and deleted some roles, now I receive this message, does anyone have any idea where do I go find this code to delele this role, it no longer exists in my cube? in the designer tool or in management studio.

thank you!

Analysis Services security is based on the role objects that belong to a database. At the same time, individual objects like cubes and dimensions also contain additional security related objects that reference database role objects. If you set permission on a dimension and then you get XMLA script of dimensions defition, you will see DimensionPermission object as part of a XMLA script.

This DimensionPermission object would look like:

<DimensionPermission>

<ID>DimensionPermission</ID>

<Name>DimensionPermission</Name>

<RoleID>Role</RoleID>

<ReadDefinition>Allowed</ReadDefinition>

<Read>Allowed</Read>

</DimensionPermission>

As you see it references RoleID.

Most likely in your case there is a leftover permission object that references non-existent role. Script your database as XMLA and try to find which Permission object is referencing your Sales_Marketing role.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you, my role is defined in my desktop tool, not on the server which will not allow me to build or deploy my cube. My permission looks like this, one is object ID Role and the second is Role 1. The one I deleted was probably Role 2 but I don't see where that is documented in the xml, I guess I could bring my deployed cube back to my desk top but I wanted to resolve the issue since I had made calculation changes there; Any ideas on how I can identify it? thank you!

</Dimension>

</Dimensions>

<CubePermissions>

<CubePermission dwdesign-time-name="34abb275-a50e-48ab-89b1-bb47d248d4a3">

<ID>CubePermission</ID>

<Name>CubePermission</Name>

<CreatedTimestamp>0001-01-01T08:00:00Z</CreatedTimestamp>

<LastSchemaUpdate>0001-01-01T08:00:00Z</LastSchemaUpdate>

<RoleID>Role</RoleID>

<ReadDefinition>Basic</ReadDefinition>

<Read>Allowed</Read>

<ReadSourceData>Allowed</ReadSourceData>

<DimensionPermissions>

<DimensionPermission dwdesign-time-name="76086471-b808-41e6-ba68-36fef34146a4">

<CubeDimensionID>Measures</CubeDimensionID>

<Read>Allowed</Read>

</DimensionPermission>

<DimensionPermission dwdesign-time-name="d85a2673-a178-409e-a7dd-06271444acc0">

<CubeDimensionID>Dim TIME Fiscal</CubeDimensionID>

<Read>Allowed</Read>

<AttributePermissions>

<AttributePermission dwdesign-time-name="be0c4193-c0cc-49a5-b4cb-354388d59896">

<AttributeID>Calendar Quarter</AttributeID>

<AllowedSet>{}</AllowedSet>

</AttributePermission>

</AttributePermissions>

</DimensionPermission>

</DimensionPermissions>

</CubePermission>

</CubePermissions>

|||

You can try to fix your project by opening the project files directly and finding reference to the offending permission object there.

Find the .cub file that contains definition of your cube and see if you can fix it.

Word of caution, it is very easy to mix up XML when editing it. Please make sure you make a copy of your project files before you edit them.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

Thank you for the information, I just rebuilt it from the server, I thought it would be a good thing to know how to repair, but don't have the time for reviewing any more xml, you are correct, it is very easy to mix it up.

Thank you for the help.

Reuse deleted primary key id

Hello,

Can I reuse the deleted primary key id? I'm using SQLServer2005.

cheers,
imperialx

Yeah, that is no problem.

|||

Hi jamesqua,

I should have asked like this, "How can I reuse the deletedprimary key id?"

cheers,
imperialx

|||

Can i no u r exact requirement. deleted primary key id means ?

if its a value no problem. U can reuse it.

I think There is no way to store the deleted value(ofcourse u can place into a temp table or another. Incase of procedures its different).Plz check out

Exact wt i need is u r requirement . How u want to deal it.Explanation needed (Thank u)

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Hi srijaya,

srijaya_ramaraju@.yahoo.com:

...Exact wt i need is u r requirement . How u want to deal it.Explanation needed (Thank u)...

The primary key is incremented by 1 in every addition of data by default, letsay the primary key value is 56, if this row is deleted then I couldn't use thevalue 56 because the next primary key value will be 57 when adding a new data.

My tablewill now look like so, as you can see the id field values aren't sequentially accumulatedby 1.

id |username
1 | Tedd
2 | Scott
3 | Bing
57 | Mich
58 | Jake
76 | Jenn
77 | Lea

Can SQLServer2005 reuse the deleted primary key for me to have a sequencial value for my id fields?

Hope I explain my problem plainly.

cheers,
imperialx

|||

U r primary key column is defined with Identity property. Thats the reason u r Primary column id will be incremented by 1(or as per the seed). Once we removed that entry. I think its not possible(99.99%). So choice is u rs. U want to go with identity column u can. or manually u have to write the code.

Plz correct me if i m wrong.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

If your primary key is an identity column you can't reuse it. You have to rely on the number generated by sql server and can't put your own values in there. If this behavior does not meet your requirements I would suggest going with a non-identity public key.