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

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 with dual role of time dimension

I'm fairly new to SSAS, so forgive me for asking a question that’s probably quite easy to answer, if you know your way around cubes and dimensions.

The situation is this: I'm creating a cube for resource planning (dimensions include resource/team, project/customer and time, fact table include number of bookings and value of bookings/projects). The time dimension plays two roles:

1) The booking date. Meaning that ressource1 from Team A is booked for project X on given dates in the future.

2) Reporting date. The complete plan is loaded into a Data warehouse once a day. The reporting date should allow us to compare the current situation with a previous point in time (compare booking ratio, value of future projects etc). In effect the reporting date is the versioning of the plan.

I'm currently facing two problems with the dual role of the time dimension:

1) How can you make the Reporting date semi-additive without also affecting the booking date (booking count and value should be summed across booking dates for a given reporting date, but not across reporting dates)?

2) How can you set default values for reporting dates? - Default I would always want to see data for the latest reporting date (corresponding to the latest version of the plan). But this should not affect the booking date - for the latest version of the plan, I want to see all future bookings.

I guess these are common problems when building a planning cube with time versioning, so I hope somebody has some valuable input.

Thanks in advance.

Thomas N. S?rensen

Hi Thomas,

Here are links to past threads in this forum that could help you:

For problem 1) relating to semi-additive measures when there are multiple time dimensions:

lastnonempty & role playing date dimension

>>

Hello,

I'm just curious if in case of a role-playing date dimention it's possible to somehow tell SSAS to use only one role for LastNonEmpty aggregate?

Like we have a fact table with a few date related members - such as TransactionDate, DateOpened, DateClosed etc.

All measures in this fact table are set to aggregate as LastNonEmpty & everything works just fine as long as only TransactionDate is linked to dimDate.

If any other dates are linked then LastNonEmpty doesn't work properly anymore & we get unpredictable results.

So if it possible to set ONLY TransactionDate to be used as LastNonEmpty & for all other dates just aggregate as sum?

Thanks!

...

It is always only ONE role-playing time dimension. The problem is, you cannot control which one it is. Sorry, but there is no way to tell directly to SSAS which one. You can keep reordering the cube dimensions in AMO until SSAS picks the right one. After that, if the order doesn't change - it will always use the same one (it is stable algorithm w.r.t. order of dimensions).

---

Mosha - http://www.mosha.com/msolap
...

>>

For problem 2) relating to different default members for each role of a dimension:

Different Default Members for Role Playing Dimension

>>

...

You could try updating the default member for each role with cube MDX script statements instead, like:

Code Snippet

ALTER CUBE CurrentCube UPDATE DIMENSION [Base UOM].[UOM Abbrev],

DEFAULT_MEMBER = [Base UOM].[UOM Abbrev].&[Abbrev1];

ALTER CUBE CurrentCube UPDATE DIMENSION [Reporting UOM].[UOM Abbrev],

DEFAULT_MEMBER = [Reporting UOM].[UOM Abbrev].&[Abbrev2];

>>

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...