MSSQL2000: get list of role members

Question

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

Accepted Answer

You can use the following stored procedures:

For fixed server roles, the stored procedure is sp_helpsrvrolemember:

```
exec sp_helpsrvrolemember 'role'
```

For general roles, the stored procedure is sp_helprolemember:

```
exec sp_helprolemember 'role'
```

MSSQL2000: get list of role members

Solution

Problem