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.

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)

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!

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

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!