September 19, 2008 at 11:03 pm
I have members in some of my user defined database roles that cannot be deleted. When I right click on the role and choose properties the members window shows 16 members. When I run sp_helpmember or look in the sys.database_role_members view there are only 4. When I delete the member from the properties window it shows it deleted until I open the properties window again. Where are these additional items coming from and how can I get rid of them? I know it happened after we moved to a new server and it is related to the orphaned database users. I cleaned those up with some scripts I found on the net, but it didn't clean up the roles. Any suggestions? I'm using SQL Server 2005. Windows NT groups are logged into the SQL Server and the SQL Server logins are set to databases. The database logins are set to user defined database roles.
May 10, 2011 at 2:20 am
I have the exact same problem.
I removed some users from several roles but they still appear in the list of role members in SQL Management Studio.
The user '3' was removed from role 'db_datareader' using sp_droprolemember as shown below
EXEC sp_droprolemember N'db_datareader', N'3'
If I run the following script with my account (db_owner), is_member() says that user is a member but sp_helprolemember does not list the user as a member.
execute as user = '3'
select is_member('db_datareader'),
exec sp_helprolemember 'db_datareader'
revert
I have googled for the past 2 hours but I was not able to find anything. Anybody has seen the same odd behavior?
Is there anyway I can have SQL Management Studio displaying the correct role members?
May 10, 2011 at 2:38 am
One of my colleagues found that it is because this user is member of another role, which is a member of 'db_datareader' role.
Lesson learnt: SQL Management Studio displays as role members all users who are a direct member of the role and users are "indirect" members (in the case of nested roles)
May 10, 2011 at 3:52 am
Execute following command to find out orphan users if you found any fix it and than delete.
sp_change_users_login 'report'
Good Luck!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 10, 2011 at 3:58 am
Thank you for your reply! This is not due to orphan users but how Management Studio works (see my previous reply)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply