Problems with roles after migration from AS2000 to SSAS2005

Hi All,

I am having trouble with the access roles after migrating from AS2000 to SSAS2005. The automated migration did not seem to work so I am trying to get it to work manually and hopefully learning something in the process.

Limiting access to dimension members/hierarchies
In AS2000 we relied heavily on limiting access on certain dimensions, e.g. to allow certain parts of the organization to only access parts of the organization dimension. Thus 'Europe' would only have access to 'Europe' and all European countries below.

I think I understand how to implement this in SSAS2005 also and I get the desired behaviour.

Limiting access to measures
However, in AS2000 we also limited what measures should be shown in the virtual cubes. This was done by disallowing access to some of the measures in the underlying cubes and the calculated measures contained in the virtual cube would simply not be accessible.

This behavior I don't seem to be able to replicate in SSAS2005.

This is my current setup:
One Order cube with one measure and five calculated measures.
The measure are linked into another cube, i.e. Sales.

In the Sales cube I want to remove the access to the measure and the five calculated measures for one role.

In AS2000 the role I just denied access to the measure in the virtual cube and the calculated measures based on this measure were hidden as well. I can't seem to do that here.

I have tried the following:

I have tried to deny the calculated measures in the Sales cube directly using "Advanced". "Denied member set". In the MDX-editor this went through without a complaint but it did not seem to have any effect. Additionally, when I opened the role through Management Studio it complained that it the members did not exist or something similar. :-(I have tried to deny access to the original measure in the Order cube (the cube I am linking from) as well as the calculated members to no avail and with the same result.

I am at a loss here. This was so simple in AS2000. I must be missing something obvious.

Hope you can help me.

Best regards

/A

The reason it worked like you described in AS2000, because AS2000 ignored the MDX commands with errors. Therefore when measure was secured, the calculated measures which were based on it raised errors, and AS2000 ignored these calculated measures, so the effect on end user was that these calculated measures effectively disappeared, so it looked like they got secured as well.

To get the same behavior in AS2005, you need to set ScriptErrorHandlingMode property of the cube into IgnoreAll value.

|||Ah, great, thank you!

I would never have figured that out for myself.sql

Problems with change sql permissions after migrating domain user/group accounts into root domain

I have a root domain and child domain.

After using ADMT to migrate the domain user or group into the root domain, when I use enterprise manager to try and change the permissions allocated to that domain user/group, i get the 'Error 15401 NT user or Group not found'.

This is a correct error as the user is now in the root domain, however sql (in sysxlogins) still thinks its in the child domain.

Is there a simpler way, other than collecting the users permissions, deleting the user from SQL then adding back in with the correct domain\username format, then adding the permissions back?

I tried renaming the 'name' in sysxlogins (not recommended) and while that worked, whenever I tried to add the migrated user to another database, the login name was missing and would not resolve.

I believe it is something to do with the SID not matching.

Any ideas on how to fix this ?

You're correct, different SIDs.

Here are some articles that may help.

http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins