AMO:Problem when dropping a role from Analysis Server 2005 by using .Net Programming and AMO

HI All,

I am working on Analysis services 2005 and managing with AMO in .Net 2.0.

Here am able to create a role and drop a role if there is no permissions to the role but in case role is having any permissions am not able to Drop the role from the database.Could any one please tell me how to remove the permissions on that role by using AMO and .Net.

Thanks in Advance,

vishwesh

Have you tried searching the DatabasePermissions collection of the database for Permissions referring to the Role in question, and removing each of these from the database?

Programming AMO Security Objects

...

A role cannot be dropped if there is any Permission object associated with the role. To drop a role, all Permission objects in the Database objects must be searched, and the Role removed from permissions, before the Role can be dropped from the Database.

|||

Hi

I gone through the document but can you send sample code how to remove the database permissions .

|||

I don't have such a working sample at the moment - maybe someone else on the Forum does?

AMO:Problem when dropping a role from Analysis Server 2005 by using .Net Programming and AMO

HI All,

I am working on Analysis services 2005 and managing with AMO in .Net 2.0.

Here am able to create a role and drop a role if there is no permissions to the role but in case role is having any permissions am not able to Drop the role from the database.Could any one please tell me how to remove the permissions on that role by using AMO and .Net.

Thanks in Advance,

vishwesh

Have you tried searching the DatabasePermissions collection of the database for Permissions referring to the Role in question, and removing each of these from the database?

Programming AMO Security Objects

...

A role cannot be dropped if there is any Permission object associated with the role. To drop a role, all Permission objects in the Database objects must be searched, and the Role removed from permissions, before the Role can be dropped from the Database.

|||

Hi

I gone through the document but can you send sample code how to remove the database permissions .

|||

I don't have such a working sample at the moment - maybe someone else on the Forum does?

AMO:How to create a new role by using vb.net

Hi,

I want to know is it same to create a role in Analysis Server 2000.why because there we are creating a role in Database as well as in Cube.i want to know in AS 2005 also we need to create a role like the same.

If any one knows please help me on this.

Hi,

There is a thread about creating roles with AMO here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1795829&SiteID=1

In AS2005, you need to create one Role in the Database (where you will define the users) and then Permissions as needed: DatabasePermissions, DataSourcePermissions, DimensionPermissions, CubePermissions, MiningStructurePermissions and MiningModelPermissions.

Adrian Dumitrascu

AMO:Can any one knows how to drop the permissions

HI all,

I am working on AMO and .NET.In my application i have to create a role and that i am able to do it.When i am creating a role i provided some Database and cube permissions.while dropping the role i am getting some error message is like permissions are exist on that object(ROLE).So what i did is i dropped permissions first after that i am tried to drop the role.

Still i am getting the same problem when i am dropping the permissions.I am sending that part of code which i am using in my application.

dbPerm = amodatabase.DatabasePermissions.FindByRole(RoleName)
dbPerm.Drop(DropOptions.IgnoreFailures)
cubePerm = amocube.CubePermissions.FindByRole(RoleName)
cubePerm.Drop(DropOptions.IgnoreFailures)
Newrole.Drop(DropOptions.IgnoreFailures)

after dropping the role i am commiting the transaction.and i am getting below error message while deleting.

Errors in the metadata manager. The cube permission with the ID of 'CubePermission 3', Name of 'CubePermission 3' was invalidated by operations in the transaction. Errors in the metadata manager. The transaction cannot be committed because one or more objects were invalidated during the transaction

Please help me on this and correct my code.

Regards,

vishwesh

Set Newrole.Drop(DropOptions.IgnoreFailures) to Newrole.Drop(DropOptions.AlterOrDeleteDependents).

I believe you can remove the following:

dbPerm = amodatabase.DatabasePermissions.FindByRole(RoleName)
dbPerm.Drop(DropOptions.IgnoreFailures)
cubePerm = amocube.CubePermissions.FindByRole(RoleName)
cubePerm.Drop(DropOptions.IgnoreFailures)

B.

|||

Hi Bryan,

I tried with the above code and executes but i am getting some strange error see below.

Errors in the metadata manager. Impact analysis inside a pending transaction cannot support CREATE, DELETE, or ALTER commands.

could you please let me know what could be the problem.

Regards,

vishweshwar

|||

Could you post your revised code?

Thanks,
B.

|||

Hi Bryan,

Please find the revised code.

Newrole = amodatabase.Roles.FindByName(RoleName)
If Newrole Is Nothing Then
Throw New ApplicationException("Role not exist in database")
Else
Newrole.Drop()
amodatabase.Update()
amoserver.CommitTransaction()
End If

Please help me on this how to sort it out.

|||

Bryan's suggestion was to use the AlterOrDeleteDependants option on the Drop method.

Newrole = amodatabase.Roles.FindByName(RoleName)
If Newrole Is Nothing Then
Throw New ApplicationException("Role not exist in database")
Else

Newrole.Drop(DropOptions.AlterOrDeleteDependents).

amodatabase.Update()
amoserver.CommitTransaction()
End If

AMO:Can any one knows hoe to rename a role and permissions to that role

Hi All,

I am working on AMO and vb.net.Here with this I am managing Analysis Server 2005 with AMO.

My problem is , am able to create a role with role members . but when i try to rename a existing role i couldnt see any direct methods available with AMO.So what i did is first i created a role with normal procedure and then after , i am planning to drop the existing one.

here when i did this i am getting some errors while dropping the role like meta data permissions are not able to drop.

so could any one come across this kind of situation please help me.

your help is valuable to the go further.

Regards,

vishweshwar.P

Renaming a role is simply a matter of getting a reference to the role, changing the name property and calling the update method.

role = db.Roles.GetByName("Old Name");

role.Name = "New Name";

role.Update();

|||

Thanks Darren.

AMO:Can any one knows hoe to rename a role and permissions to that role

Hi All,

I am working on AMO and vb.net.Here with this I am managing Analysis Server 2005 with AMO.

My problem is , am able to create a role with role members . but when i try to rename a existing role i couldnt see any direct methods available with AMO.So what i did is first i created a role with normal procedure and then after , i am planning to drop the existing one.

here when i did this i am getting some errors while dropping the role like meta data permissions are not able to drop.

so could any one come across this kind of situation please help me.

your help is valuable to the go further.

Regards,

vishweshwar.P

Renaming a role is simply a matter of getting a reference to the role, changing the name property and calling the update method.

role = db.Roles.GetByName("Old Name");

role.Name = "New Name";

role.Update();

|||

Thanks Darren.

AMO: Role Member not existing in Active Directory

Hi,

I have a function in VB.Net that checks if role member is existing in Role. It's doing well, however, if role member is not existing in Active Directory it would prompt me this error during role update:

No mapping between account names and security IDs was done.

So, i provided an on error-resume-next error handler just for it to ignore the error. However, in the succeeding loops, eventhough the role member is existing in AD and not existing in Role, it's prompting me the same error each time it does a Role Update.

Was the error cached the first time? How do I manage this situation well?

cherriesh

Are you saying the user account did exist in AD, was added to your SSAS role, and then removed from AD which gives you the initial error? If so, was the user account disabled in AD or dropped from AD?

B.

|||

Hi,

the domain name is maintained in a specific table in the database which the user had keyed in. I access this table to add the domain name in my SSAS role. However, if the domain name is not really a real one or was entered with typo error, the SSAS will prompt an error at Role1.Update() command since the account entered has no match in AD. If i resume-next on this error, in the succeeding role update, it will prompt me the same error.

cherrie

|||

One of my guys ran into a similar issue on an AMO script. Here is the code he used in the script to resolve the problem:

Code Snippet

Try
currentRole.Members.Add(New RoleMember(RTrim(LTrim(dr(1).ToString))))
currentRole.Update()
Catch ex As Exception
currentRole.Refresh()
End Try

Hope that helps,

B.

AMO: No mapping between account names and security IDs was done for local users

Hi,

Analysis Services 2005 and SQL Server 2005

Using AMO I'm trying to add a local user to an Analysis Services database Role that I created but I keep getting the error "No mapping between account names and security IDs was done". I can add the user to the Role with no problem using SQL Server Management studio, but if I try to add the user using AMO I get that error.

The local user that I'm trying to add belongs to both the local User and Administrator groups. I've added a logon for this local user to the corresponding SQL Server database that the Analysis Services database datasource points to, but still no luck.

Am I missing something? I've been doing this for the last three months with network users without a problem so this is completely frustrating me. Especially the fact that I can add them using the SQL Server Management Studio GUI.

Here's the basic code that I'm doing. Pretty straightforward.

private bool AddUserToAnalysisServicesRole(Role role)

{

string user = @."pdxvincentm02\testuser";

RoleMember rm = new RoleMember(user);

role.Members.Add(new RoleMember(user)); // e.g. redmond\johndoe

role.Update(); // << KABOOM!!! No mapping between ......

Help. Thanks

I have not had a chance to try this in C# yet, but I did a quick test in powershell using the following pattern and it worked.

role.Members.add("pdxvincentm02\testuser")

role.Update()

It's possible that the role.Members.Add() function has another overload that works a bit differently. But another though also occurred to me. Where is this code being executed from? Is it possibly running under an account that does not have permission to verify the account? (eg. is it running from an ASP.NET app running as local system?)

AMO: Convert string to Role and Role Member

Hi,

I have a functionality in my AMO that deletes role member from a specific role but first I must check if that role member is existing in that role before calling any delete.

This is the function that checks if role member is existing:

Function RoleContains(ByVal MyRole As Role, ByVal MemberName As String) As Boolean

For Each rm As RoleMember In MyRole.Members

If String.Compare(rm.Name, MemberName, True) = 0 Then

Return True

End If

Next

End Function

However, the one i will pass to the parameters are taken from the textbox of vb.net. so the data type is string and i'm having a problem passing this to Role object.

how can i do that?

cherriesh

The role collection includes a FindByName() method. So, if you were after a database role object, you can get a role object from it's name by doing something like:

myRole = db.Roles.FindByName("Test Role")

AMO: Check if role member is existing in Role

Hi,

In AMO, how do I check if role member is existing in Role? I have this command (VB.net):

If UserRole1.Members.Contains(mydatareader(0)) = true then

.

.

end if

But it keeps having an error:

Unable to cast object of type 'System.String' to type 'Microsoft.AnalysisServices.RoleMember'.

How do i check properly?

thanks .

cherriesh

The contains function expects a MemberRole reference, not a string. I can't see anyway of checking directly if a role contains a given member name so you would have to loop over the collection. Something like the following should work. Note: I have coded this off the top of my head so I hope that I have not made any typo's but you should get the general idea.

Code Snippet

If RoleContains(UserRole1, mydatareader(0)) then

.

.

end if

Function RoleContains( myRole as Role, memberName as string)

For each rm as RoleMember in myRole.Members

'// I have used string.compare to do a case-insensitive compare

If string.compare(rm.Name, memberName,true) = 0 then

return true

End If

Next

Return false

End Function

AMO Role drop trouble

I've some trouble trying to drop roles with AMO and the following code:

Role oCurrentRole = oOLAPDB.Roles.GetByName(<role name>);
oCurrentRole.Drop(DropOptions.AlterOrDeleteDependents);

Most of the time it's OK but I've sometimes this error

Exception non gre : System.InvalidOperationException: Impossible de supprimer l'objet ? Role ?my role , il doit faire partie d'un objet Server connect.
à Microsoft.AnalysisServices.MajorObject.DropPrivate(DropOptions options, XmlaWarningCollection warnings, ImpactDetailCollection impactResult, Boolean analyzeImpactOnly)
à Microsoft.AnalysisServices.MajorObject.Drop(DropOptions options)

(in english: Unhandled exception : System.InvalidOperationException: Unable to drop ? Role object ?my role , it must be part of a connected Server object)

I'm the only one connected to this server and I'm sure this role isn't used.

One thing to mention: It seems I've this error only with roles with members which are no more existing in the domain (they were when the role was created but get deleted later).

Any idea ? How could I drop these roles with AMO ?

David,

I created an account on my local system, JunkUser, and then created a role in Adventure Works DW called JunkRole. JunkUser is the lone member of this role. I then dropped the JunkUser account from my system.

In SSIS, I created a package with a single Script task. I added the AMO reference and an Imports statement to simplify my code. In Sub Main, I added this code:

Code Snippet

Dim MyServer As New Server

MyServer.Connect(".")

Dim MyDatabase As Database = MyServer.Databases.GetByName("Adventure Works DW")

Dim MyRole As Role = MyDatabase.Roles.GetByName("JunkRole")

MyRole.Drop(DropOptions.AlterOrDeleteDependents)

MyServer.Disconnect()

I executed the SSIS package. It completed successsfully and I verified the JunkRole had been dropped.

I am running on SP2.

Not sure what might else may be causing the problem you are seeing.

B.

|||My bad

You're right Bryan, it doens't matter if the user has been deleted or not

In fact, I had a nice
oServer.Disconnect();
just before my code... And of course, oServer is my OLAP Server object... Now that I'm thinking about it, the error message was quite explicit ("...connected Server")

AMO Role Creation

Hi,

I have just created a role using AMO. I'm supposed to see this role in the Analysis Services Project once I open it, right? But I cannot. I tried executing again my codes, and it prompted me an error, which means that it was created successfully.

Error:

Another 'Role' object has the 'TestRole' name.

Why can I not see that in the AS Project?

By here is my code:

Svr = New Server

Svr.Connect("Data Source=localhost")

DB = Svr.Databases("AS17")

Cub = DB.Cubes("Test")

UserRole = DB.Roles.Add("TestRole")

UserRole.Members.Add(New RoleMember("xa\chemps"))

UserRole.Update()

thanks a lot!

cherriesh

The role you created using AMO will exist on your server. If you are using a standard project (not in online mode) you will not see changes that are made on the server. You can use the "import 9.0 database" option in BIDS to create a new project based on a database on the server, or you can connect BIDS directly to the server in online mode or use SSMS to see the role. Changes made to the server are not automatically propogated back to a project.

|||

Hi,

The server is my localhost. How do i know if my project is in online mode? I already deployed and processed it before i created the roles in AMO and run it. In SSMS, where can I find there the role?

Thanks a lot!

cherriesh.

|||

If from BIDS, you go File -> Open Analysis Service Database and open the database from your localhost, then you are in online mode, but if BIDS was open when you created the role you would need to refresh or re-open to see the new role.

But if are doing a file -> open project, or clicking on an entry in the recent projects list, then you are in off line mode (which is the recommended way of working)

In SSMS you connect to the SSAS server, expand the databases in the object browser and then expand the roles. Once again, if you create the role while SSMS is open you would need to refresh the roles node.

Amendment to question.

Is there a way to remove the rights to add a new server login from a sys
admin server role member?
TIA,
JoeYou can't deny anything to a sysadmin - they can literally do anything on
the instance and any other permissions are ignored
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:45188460-768D-4806-9F6F-A9A53B9AC84A@.microsoft.com...
> Is there a way to remove the rights to add a new server login from a sys
> admin server role member?
> TIA,
> Joe
>

Am I being role happy?

I'm working on the security portion of this vb app. In the application the
user is presented with a list of commands that can be executed on a piece of
data.

My client wants to be able to block certain users from seeing certain
commands on their screen.

The commands are loaded into a listview from a table.

I've created a role for each combination of command/province(A user may be
able to execute a command on an Ontario piece of data but not a Quebec
piece).

I just modified the query that loads the list box so that it includes an
IS_Member with the command/province combination.

The roles were easy to create(sql query to create the sp_addrole commands).
And the query modifications were easy as well. Is_Member barely affects
query times(very fast).

I've also created roles that contain these roles so a user only has to be
added to one role to get access to large groups of commands.

I may have one problem though. I've created hundreds of roles on the
server. Is that a problem? Can anyone tell me if there's a better way of
doing this?

Thanks in advance.
J
P.S. The application has just under 500 users and is going to hit 1000 in a
couple of years.Me (me@.here.com) writes:
> I've created a role for each combination of command/province(A user may be
> able to execute a command on an Ontario piece of data but not a Quebec
> piece).
> I just modified the query that loads the list box so that it includes an
> IS_Member with the command/province combination.
> The roles were easy to create(sql query to create the sp_addrole
> commands).
> And the query modifications were easy as well. Is_Member barely affects
> query times(very fast).
> I've also created roles that contain these roles so a user only has to be
> added to one role to get access to large groups of commands.
> I may have one problem though. I've created hundreds of roles on the
> server. Is that a problem? Can anyone tell me if there's a better way of
> doing this?

The one thing that I don't really like this design is that you use
SQL Server's own mechanisms to control data access within the
application. I would prefer to have my own authorization tables.
This design can confuse a DBA who thinks roles are for control access
to tables and T-SQL commands.

Then again, if it ain't broke...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp