What Problem Does This Solution Solve?
Manipulating Users security in Role model of Microsoft SSAS Cube.
How Does This Help Someone?
Using just the solution, you will be able to add/remove users to defined Roles in SSAS Cube, and by using all library functions you can manipulate the SSAS Cube entirely except cube design.
Background
You should be aware of C#, and SSAS roles.
How Does the Code Actually Work?
You should reassign the variables of LDAP path, and change the server name, and Cube name in the app.config to make it work. then build and run.
What is Going Inside the Code Snippets?
I had 3 main problems to manage this roles task.
But before that, you need to add these DLLs into your solution.
Microsoft.AnalysisServices
- for dealing with Cubes System.DirectoryServices.AccountManagement
- for dealing with Active Directory
These DLLs will help a lot for dealing with cube structure - you can do a lot than in this article - and also dealing with Active.
1. Connecting to the Cube Server (aka: SSAS Server)
using Microsoft.AnalysisServices;
Server server = new Server();
server.Connect(ConfigVals.ServerName);
Database db = server.Databases.FindByName(ConfigVals.CubeName);
if (db == null) throw new Exception("Failed To Connect!, <br /> please check that you have access to the server and cube.");
Now you are connected to a specific cube from the server.
Note: You can get the list of all available cubes relative to your security from the server and check what you can do with this object on your own.
2. Checking User Names from Active Directory
This part should be easy to search, but take care of the LDAP directory name:
string ldapAddress = "LDAP://<<SERVER>>";
DirectoryEntry de = new DirectoryEntry(ldapAddress);
DirectorySearcher ds = new DirectorySearcher(de);
ds.Filter = "(samaccountname=" + memberName + ")";
SearchResult rs = ds.FindOne();
Make sure that member does not contain "\" sign. as it searches only for the login name. If you have multiple domains don't be afraid, it takes care of it.
3. Manipulating Role Security
For this task, I had 2 goals:
- Adding New User
- Removing existing User
3.1 Adding New Users
Role role = GetRole(roleName);
string member = string.Format("{0}\\{1}", DomainName, memberName);
if (!IsMemberInRole(role, member))
{
role.Members.Add(new RoleMember(member));
role.Update();
role.Refresh();
}
else
throw new Exception("User already exists!");
GetRoles
is a helper function to get a role by name, so it is no big deal.
IsMemberInRole
should take attention as you will find in Role
class a property called Members
and this is a collection. So theoretically speaking, you can search in it by contains function, but the problem is that it is searching by 2 properties of the member (Name
, and SID
) and SID
is a guid. So, I create a helper function to iterate through members collection and return back the matching member.
Role role = GetRole(roleName);
bool IsMemberInRole(Role role, string member)
{
if (role == null || string.IsNullOrEmpty(member) ||
string.IsNullOrWhiteSpace(member)) return false;
foreach (RoleMember item in role.Members)
{
if (item.Name.ToLower() == member.ToLower())
return true;
}
return false;
}
Finally, you need to call Role.Update()
to reflect the changes into server and then call Refresh
to reflect what is on server to the class properties.
3.2 Removing Users
Role role = GetRole(roleName);
role.Members.RemoveAt(GetMemberIndexInRole(role, memberName));
role.Update();
role.Refresh();
The same concept for removing a user.
Points of Interest
There are some tricks you should take care of while executing the code.
- Change the target framework from ".NET Framework 4 Client Profile" to ".NET Framework 4 Client Profile"
-
Microsoft.AnalysisServices
is not listed by default, you should get it from C:\Program Files\Microsoft.NET\ADOMD.NET\100.