Users of a role

[Mr. Tomaszek]

Hi All,

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

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.

[forgottenhart]

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

create procedure .dbo.mp_returnroles
@role

as
Select myrole_id
from mytable
where @role = myrole_id
go

the call to the stored procedure

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

[andreasblixt]

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:

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:

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):

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