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!