Problems with Roles and sp_helpRoleMember

I have a database role named gc_stationAdmin. I have a user x who has this role granted him when via Mgmt Studio under Logins. In fact I have 50+ users who have this role.

1) when I execute sp_helpRoleMember I see only 20 users who have this role when I'm expecting to see 50 and user X is not amongst the 20

2) when sp_helpRoleMember is executed AS user X, they only see 1 user with this role (and it's not himself)

3) when I drop user X from Role using sp_dropRoleMember OR using Mgmt Studio under Login Properties, it never drops him according to Mgmt Studio. When I pull up the login properties, that role is still always checked no matter what I do...although he doesn't appear in the sp_helpRoleMember result ever.

What is going on with this? Why is dropping role member not effective? And how can I get user X to appear in the resultset of sp_HelpRoleMember?

Thanks in advance for any help...this is totally confusing me.

Sounds like there is a disconnect between the user in the server and the user in the database. Is this Windows authentication or SQL Server? Which version?

There are three tables involved with sp_helprolemember. You can cut to the heart of the matter by running this query:

select DbRole = g.name, MemberName = u.name

from sys.database_principals u

, sys.database_principals g

, sys.database_role_members m

where g.name = 'gc_stationAdmin''

and g.principal_id = m.role_principal_id

and u.principal_id = m.member_principal_id

order by 1, 2

My guess is that they won't show up there. Check the three tables one at a time to see what ID's and so on that they have. That might point you in the right direction.

|||

This is a windows authenticated user. Based on previous posts in this forum, I've already looked at those 2 tables (principals and rolemembers). Indeed, the guy's principal ID never is added to the rolemembers table when I check off the role for that guy's login or use sp_addrole. Either I don't have permissions (which I do - Admin) or something is overriding this guy's role configuration.

And why would he only see 1 from sp_helprolemembers and I see 20? Please help me make some sense on what's happening...

(thanks for your reply Buck)

|||

Can you specify what version of SQL Server you are using?

Thanks
Laurentiu

|||Sorry...using SQL 2005.|||

The reason you see more results than x, when calling sp_helprolemember, is related to catalog security. But why x does not show in the results of sp_helprolemember even though you explicitly call sp_addrolemember is the puzzling part.

Are you getting any errors when calling sp_addrolemember?

Thanks
Laurentiu

|||Absolutely no errors at all. SQL states it executed successfully.|||

I would suggest to check the permissions catalog, to see if there are any denies that impact your ability to see the membership rows:

select * from sys.database_permissions where state <> 'G'

Also, does the membership actually take effect? If you connect as that user, does is_member return 1?

Thanks
Laurentiu

|||

Thanks Laurentiu...there were no explicite denies in the database_permissions table. Is_Member indeed shows that this user is a member. So, I guess I need to be using Is_Member rather than sp_HelpRoleMember. I've been using HelpRoleMember for the past 8 years with no problem until now. And now it works for some and not for others. I still think there is a problem, however.

In fact, I tried sp_HelpRoleMember again just now as I was typing this reply. Recall, previously when I executed this (as the user) I would get 1 record return and it wasn't him. Now it shows 2 records and he's one of them. The only thing I did was execute your recommendations (select * from sys.database_permissions ... AND Is_member). So, I really have to wonder what Is_Member is doing because this appears to have "kicked things" back in order. I can verify this on other user accounts having this problem...

Thanks again for having me try these suggestions!

|||

Is_member should not have any effect on sp_helprolemember. What you describe sounds indeed like an issue, so let me make another suggestion and see if it helps: after you add the owner to the role and check with sp_helprolemember and you don't see him (if you can still repro this behavior), try clearing all caches and rerun sp_helprolemember. To clear caches, you can execute:

dbcc freesystemcache ('all')

If you see the row after clearing the caches, then we'll have a better idea of what causes the issue.

Thanks
Laurentiu

|||Well, that wasn't it (Is_Member fixing something)...tried it on another account with the problem and it didn't fix anything. And I don't know what the heck could've fixed one user. I'm totally frustrated and at a loss. Probably need to create an incident with Microsft.|||

Yes, you can open a report on the feedback site - you can find a link to it in the sticky post at the top of this forum. But without having a repro, a report won't help much - if we cannot experience the problem, we cannot fix it. I tried a few scenarios and I couldn't see the behavior that you described.

Have you tried clearing the cache, as I suggested earlier?

Thanks
Laurentiu

|||

I did try clearing the cache to no avail.

But, forgive me for not seeing this earlier (although I don't think the problem is totally fixed). sp_addrolemember seemed to help the situation. I tried this on another user with the problem...running sp_droprolemember and sp_addrolemember.

sp_addrolemember --> executing this makes the user account appear in the results of sp_helprolemember and Is_member return 1.

sp_droprolemember --> executing this makes the user account disappear from the results of sp_helprolemember and Is_member returns 1.

Now, I use sp_HelpRoleMember from within my application. So, I think this may work for me. But I continue to be stumped why after dropRoleMember, Is_Member returns 1. I would expect it to return 0. This may explain why in SQL Management Studio, the login's role is always checked even when you try unchecking...it always comes back. And yes, I'm an Admin in those machines...we have 25 machines and it is happening on a few users everywhere. Just continues not to make sense to me.

Again, thanks for your persistent help

|||

So, clearing the cache doesn't help with the results of is_member either? Just want to confirm this...

When you execute is_member, are you logged in as the account you made a member of the role, or are you connected as admin (you can execute select suser_name(), user_name() to get your current identity).

We appreciate your time digging into this problem - if there is a problem, we want to know about it.

Thanks
Laurentiu

|||

I'm also having the same issue:

Basically, for a db_owner user it can see all member USERS, while for SQL user with a regular database role can only see the member ROLES when calling

EXEC sp_helprolemember 'My_APP_Managers'.

The reason lies deep down at sys.database_principals and sys.database_role_members. If you do a

select * from sys.database_principals

or

select * from sys.database_role_members

It comes back with different number or rows when the connected users are different.

As soon as I change the user to a db_owner, it can see all the member USERS and ROLES.

Help please!

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 roles

For my project i built a cube (with Analysis Manager). Now i had to define
roles. After i done this, i tested each role and the restrictions seams to
work. But if a user who is member of a restricted role opens the cube with
Crystal Analysis 10.0, he can see all.
How could I solve this problem?
Message posted via http://www.droptable.comAnalysis Services enforces roles for in-bound connections regardless of
client. So I am sure that it isn't AS itself. Might this be a web-based
system? If so, then the connection coming into Analysis Services is not the
client, but rather the mid-tier user who is making the connection on your
behalf.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Markus Teichmann via droptable.com" <forum@.droptable.com> wrote in
message news:7249ab2cfc8d402da66d78cc74989565@.SQ
droptable.com...
> For my project i built a cube (with Analysis Manager). Now i had to define
> roles. After i done this, i tested each role and the restrictions seams to
> work. But if a user who is member of a restricted role opens the cube with
> Crystal Analysis 10.0, he can see all.
> How could I solve this problem?
> --
> Message posted via http://www.droptable.com

problems when processing the cube

if i download a cube in a local environment to make it some changes. and then process it.. it loses its roles lets say it has this administrator role and inside this in the memberships. i added some users on the network.. when one of this users donwload the cube in his pc to make it some changes.. and the process it.. when he atempts to donwload it again.. the cube just doesn`t appear in the wizard.. the in the server where is storaged. i check the role and it has lost its properties like memberships and options like full control and so on.. why does this happens?

I suspect the role memebership is getting lost at the moment when changes are being done to the cube.

In order to make changes you open you project in BI Dev Studio. Project also contains the roles defnitions, but they are empty. So when you deploy your changes back to the server, the Role's membership is getting wiped out.

To overcome this problem you should use Deployment Wizard to make changes to your cubes. And specify in there you want Role's membership to be preserved.

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

|||DOES THIS WIZARD RUN ON THE TARGET SERVER OR CAN I RUN IT FROM MY COMPUTER DIRECTLY?|||

You run the Deployment Wizard on the local machine an point it to the project files.

It will ask you for the machine name you going to deploy your project into . That is your target server.

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

|||

thanks for your help.. it worked without any problems!!!!

regards...