MSSQL2000: get a list of role members

Question

I know the name of the role and want to find all the users in this role. How to do this in SQL Server 2000 (in a SQL script, not in Management Studio or another tool)?

Answer 1

You can use the following stored procedures:

For fixed server roles, the sp_helpsrvrolemember stored procedure :

exec sp_helpsrvrolemember 'role'

For shared roles, the sp_helprolemember stored procedure :

exec sp_helprolemember 'role'

Answer 2

Just use SQL-DMO: Replace the role name with your role

exec sp_helprolemember rolename