Users of a role

Hi All,

I want to get list of all users in particular role. It can be done by executing

Code:

---

exec sp_helprolemember 'myRole'

---

Alas it works only when user that executes that is a member of ‘myRole’.
How to achieve the same (list of all users in particular role) while user that executes that is not in 'myRole' role?

Any suggestions welcome.

not sure what lang that code is from but it looks like you are running a stored procedure. I think it is returning myrole only becasue the stored prcedure is selecting the rows that have 'myrole' . Try passing a variable to the procedure instead of the hard code

Code:

---

create procedure .dbo.mp_returnroles
@role

as
Select myrole_id
from mytable
where @role = myrole_id
go

---

the call to the stored procedure

Code:

---

private void Submit_btn_Click(object sender, EventArgs e)
        {
            myconnection.Open();

            SqlCommand Submit_btn = new SqlCommand("mp_returnroles", myconnection);
            Submit_btn.CommandType = CommandType.StoredProcedure;
           
            Submit_btn.Parameters.AddWithValue("@role", Desc_cb.Text);
           
            Submit_btn.ExecuteQuery();
            myconnection.Close();
        }

---

of course this may be totally off

Note: Since you did not specify what database engine you are using, I assumed SQL Server 2005.

Similar to how sp_helprolemember retrieves information for a certain role, you can use the system views to build your own results. Here's how you'd get a list of all members that are in roles for the current database:

Code:

---

SELECT
        dp1.name role, dp2.name member
FROM sys.database_role_members drm
INNER JOIN sys.database_principals dp1
        ON dp1.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2
        ON dp2.principal_id = drm.member_principal_id
ORDER BY dp1.name, dp2.name;

---

Here's how you get all roles:

Code:

---

SELECT
        principal_id, name
FROM sys.database_principals
WHERE type = 'R';

---

Here's how you get all users (S is SQL user, U Windows user):

Code:

---

SELECT
        name, type
FROM sys.database_principals
WHERE type IN ('S', 'U');

---