How can one user view other users that belong to a database role?

After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.

In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.

In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.

Any advice on how to duplicate the behavior from SQL2000?

The procedure relies on the security catalog views which are user sensitive, the query which is executed behind the scenes will bring back only the roles for the user.

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

from sys.database_principals u, sys.database_principals g, sys.database_role_members m

where g.principal_id = m.role_principal_id

and u.principal_id = m.member_principal_id

order by 1, 2

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for the post, however you've only explained why it's behaving as it does. I'm looking for advice on duplicating the Sql2000 behavior. For example, if I log on as user A I'd like to know if user B is a member of role ABC.|||Look what the Workbench does behind the scenes, it will execute the following commands:

set NOCOUNT OFF

CREATE TABLE #tmp_role_member_ids (id int not null, role_id int null, sub_role_id int null, generation int null)

declare @.generation int

set @.generation = 0

INSERT INTO #tmp_role_member_ids (id) SELECT

rl.principal_id AS [ID]

FROM

sys.database_principals AS rl

WHERE

(rl.type = 'R')and(rl.name=N'db_owner')

UPDATE #tmp_role_member_ids SET role_id = id, sub_role_id = id, generation=@.generation

WHILE ( 1=1 )

BEGIN

INSERT INTO #tmp_role_member_ids (id, role_id, sub_role_id, generation)

SELECT a.member_principal_id, b.role_id, a.role_principal_id, @.generation + 1

FROM sys.database_role_members AS a INNER JOIN #tmp_role_member_ids AS b

ON a.role_principal_id = b.id

WHERE b.generation = @.generation

if @.@.ROWCOUNT <= 0

break

set @.generation = @.generation + 1

END

DELETE #tmp_role_member_ids WHERE id in (SELECT

rl.principal_id AS [ID]

FROM

sys.database_principals AS rl

WHERE

(rl.type = 'R')and(rl.name=N'db_owner') )

UPDATE #tmp_role_member_ids SET generation = 0;

INSERT INTO #tmp_role_member_ids (id, role_id, generation)

SELECT distinct id, role_id, 1 FROM #tmp_role_member_ids

DELETE #tmp_role_member_ids WHERE generation = 0

SELECT

u.name AS [Name]

FROM

sys.database_principals AS rl

INNER JOIN #tmp_role_member_ids AS m ON m.role_id=rl.principal_id

INNER JOIN sys.database_principals AS u ON u.principal_id = m.id

WHERE

(rl.type = 'R')and(rl.name=N'db_owner')

ORDER BY

[Name] ASC

drop table #tmp_role_member_ids

HTH, Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-