Get list of all database users with specified role

I want to get list of all database users w开发者_开发知识库ith 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

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)

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

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]

Something like this can help you:

        select sys.server_role_members.role_principal_id ,role.name  as rolename    
        , sys.server_role_members.member_principal_id , member.name as membername 
        from sys.server_role_members 
        join sys.server_principals as role
        on sys.server_role_members.role_principal_id = role.principal_id
        join sys.server_principals as member 
        on sys.server_role_members. member_principal_id = member_principal_id;