Working with SMO sometimes lefts you amazed and adding role members is one of them. SSAS having a limit of adding members to a role and that is 1820. On adding next member throws much wearied error than expected as below.
Microsoft.AnalysisServices.OperationException: The following system
error occurred: The parameter is incorrect.
at
Microsoft.AnalysisServices.AnalysisServicesClient.SendExecuteAndReadResponse(ImpactDetailCollection
impacts, Boolean expectEmptyResults, Boolean throwIfError)
at
Microsoft.AnalysisServices.AnalysisServicesClient.Alter(IMajorObject obj,
ObjectExpansion expansion, ImpactDetailCollection impact, Boolean
allowCreate, XmlaWarningCollection warnings)
at
Microsoft.AnalysisServices.Server.Update(IMajorObject obj, UpdateOptions
options, UpdateMode mode, XmlaWarningCollection warnings,
ImpactDetailCollection impactResult)
at
Microsoft.AnalysisServices.Server.SendUpdate(IMajorObject obj, UpdateOptions
options, UpdateMode mode, XmlaWarningCollection warnings,
ImpactDetailCollection impactResult)
at
Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions options,
UpdateMode mode, XmlaWarningCollection warnings)
at
Microsoft.AnalysisServices.MajorObject.Update()
at CheckRoleCreation.Program.AddMembersToRole(Database
db, Int32 num, Role role)
…
|
On
investigating more we found that –
1. There
is limit to the members to the role and it is 1820. We can repro the same with
the below code segment.
void AddMembers(A.Database db)
{
foreach
(A.Role r in db.Roles)
AddMembersToRole(db, 1821, r);
}
void AddMembersToRole(A.Database db,
int num, A.Role role)
{
if(null != role)
{
//remove
all members from role
for (int count = (role.Members.Count-1); count >= 0;
count--)
role.Members.RemoveAt(count);
role.Update();
//add
members to the role
int i
= MemberCounter;
for (int count = MemberCounter; count < (num + i);
count++, MemberCounter++)
{
AddMembersToLocalComp(String.Format(RoleMemberName,
String.Empty, MemberCounter), false);
role.Members.Add(new A.RoleMember(String.Format(RoleMemberName,
Environment.MachineName + @"\",
MemberCounter)));
}
role.Update();
}
Console.WriteLine("Total Role Members in role {0} is: {1}",
role.Name, role.Members.Count);
}
void AddMembersToLocalComp(string memberName, bool
printMemberInfo)
{
string
path = "WinNT://" +
Environment.MachineName;
string
schema = "User";
DS.DirectoryEntry localMc = new DS.DirectoryEntry(path);
try
{
DS.DirectoryEntry newUser =
localMc.Children.Add(memberName, schema);
newUser.CommitChanges();
}
catch
(System.Runtime.InteropServices.COMException e)
{
if(e.ErrorCode
!= -2147022672) //mask new user already exists
error
throw
e;
}
if
(printMemberInfo)
foreach
(DS.DirectoryEntry e in localMc.Children)
if(e.SchemaClassName.Equals(schema))
Console.WriteLine("MemberName: {0}",
e.Name);
}
|
2.
We can have more than 1820 roles. The limit is
only on the role members (individual users or groups).
3.
We can have more than 1820 members but scattered
in various roles.
4.
The workaround for the same are many but one of
them can be adding one more role with the same type of permissions and have a
count of users before adding more than 1820 members. These situations come only
when we work more with the automated scripts.
No comments:
Post a Comment