DB roles query

[TysonLPrice]

SQL 2005

I’m an application programmer not a DBA and I’m working on a program to list all the database roles for a selected user and users in roles. I got this from Google for a user’s DB roles and I think it works:

 DECLARE @username NVARCHAR(128); 
SET @username = 'TPrice';  
WITH CTE_Roles (role_principal_id) 
AS 
    ( 
    SELECT role_principal_id 
        FROM sys.database_role_members 
     --   WHERE member_principal_id = USER_ID(@username) 
        UNION ALL 
    SELECT drm.role_principal_id 
        FROM sys.database_role_members drm 
                INNER JOIN CTE_Roles CR 
                ON drm.member_principal_id = CR.role_principal_id 
    ) 
SELECT DISTINCT USER_NAME(role_principal_id) RoleName 
    FROM CTE_Roles 
    UNION ALL 
SELECT 'public' 
    ORDER BY RoleName;

What I need now is a query to select all the users in a given role. Can one of the gurus do two things?

1. Visually I think the posted query works to give all the DB roles for a user on a given DB. Does it seem correct?
2. Using the same methodology can it be changed to give all the users in a given role?

 

[TysonLPrice]

My DBA did it for me:

Declare @role varchar(100)
set @role = 'JT_DEveloper'
with RoleRpt as
(
select g.name RoleName, u.name MemberID, rui.firstname + ' ' + rui.lastname MemberName, case u.issqlrole when 0 then 'User' else 'Role' end UserOrRole
       from   sysusers u
                     inner join sysmembers m on u.uid = m.memberuid --and u.issqlrole = 0
                     inner join sysusers g on m.groupuid = g.uid and g.issqlrole = 1
                     left join commonsql.accounts.dbo.refuserinfo rui on rui.userid = u.name
       where  g.name not like 'db_%'
union all
select r.RoleName, r.MemberID, r.MemberName, case u.issqlrole when 0 then 'User' else 'Role' end UserOrRole
       from   RoleRpt r
                     inner join sysusers u on r.rolename = u.name
                     inner join sysmembers m on u.uid = m.memberuid and u.issqlrole = 0
                     inner join sysusers g on m.groupuid = g.uid and g.issqlrole = 1
                     inner join commonsql.accounts.dbo.refuserinfo rui on rui.userid = u.name
       where  g.name not like 'db_%'
)
select RoleName, MemberID, isnull(MemberName, @role) MemberName, UserOrRole
into   #RoleReport
from   RoleRpt
where rolename = @role
order by UserOrRole desc, MemberID, RoleName
option (maxrecursion 10)

select trr.*
       from   #RoleReport trr
                     inner join commonsql.teamutility.dbo.userlogininfo uli on uli.username = trr.memberid
       where  trr.userorrole = 'User' and
                     uli.active = 1
union all
select trr.*
       from   #RoleReport trr
       where  trr.userorrole = 'Role'
order by UserOrRole desc, RoleName, MemberID

drop table #RoleReport

 

[TysonLPrice]

Can't give him a start here