Get list of all database users with specified role

Question

I want to get list of all database users with specified role. Role is a parameter for stored procedure or function.

Somethinf like a select statement with user name and its role.

+============+==========
| User name  |  Role   |
+============+==========

MS SQL Server 2008

Answer 1

Probably use something like this

SELECT
    SU.Name AS UserName, SR.Name AS RoleName
FROM
    sysUsers AS SU
      INNER JOIN 
     sysUsers AS SR ON SU.GID = SR.UID
WHERE
    SU.GID <> SU.UID
ORDER BY
  RoleName, UserName

Borrowed from SmartBihari's Blog

EDIT 1 To Get the System Roles associated with a user. The sys.sysmembers is a system view which has the memberuid and the groupuid as the only columns. you can use the user_name() function to retreive the name of each column.

USE [YourDatabase]
SELECT user_name([memberuid]) as [Username], User_Name([groupuid]) as [Role_Name]
FROM [sys].[sysmembers]

Answer 2

just depending on how you want to pass the parameter...assuming you'll use the id of the role

declare @roleID int

select
    role_principal_id as roleID, 
    user_name(role_principal_id) as roleName, 
    member_principal_id as memberID,
    user_name(member_principal_id) as memberName 
from 
    sys.database_role_members
where 
    role_principal_id = @roleID

Answer 3

In SQL 2005 and 2008 this information is most easily accessed in two catalog views.

This query should give you the information you're looking for.

select rp.name as database_role, mp.name as database_user
from sys.database_role_members drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)