SQL 2005 find all users in a DB role

Question

SQL 2005 find all users in a DB role

tyson.price

SSCrazy

Points: 2093
More actions
June 17, 2010 at 5:14 am

#222875

I got this Query here in the scripts forum. It lists all the roles a user is in. What I also need is a query to list all the users in a given role. Does anyone have one?
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;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i have this saved in part of my snippets; it's scripting all the users for every role ; you could parameterize it to limit it to one role if desired:

--example results:

EXEC sp_addrolemember 'db_datareader','bob'

EXEC sp_addrolemember 'db_datawriter','bob'

EXEC sp_addrolemember 'db_owner','bob'

EXEC sp_addrolemember 'db_owner','superman'

EXEC sp_addrolemember 'MSmerge_PAL_role','MSmerge_52F065AA4F264D1F9263ED13CF6DB249'

SELECT 'EXEC sp_addrolemember ''' + Rtrim(r.name) + ''',''' + Rtrim(Coalesce(l.name,u.name)) + '''' AS '-- Add Role Users'

FROM dbo.sysusers u

INNER JOIN sysmembers m

ON u.uid = m.memberuid

INNER JOIN sysusers r

ON m.groupuid = r.uid

LEFT OUTER JOIN MASTER.dbo.syslogins l

ON u.sid = l.sid

WHERE r.issqlrole = 1

AND u.name <> 'dbo'

AND r.name LIKE '%'

ORDER BY r.name,

u.name

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tyson.price SSCrazy Points: 2093 More actions · Answer 2

I’m not a DBA, I’m in applications and I’m trying to report on what users are in what roles. The structure is there are roles set up as job titles. Each job title has roles. For example:

JT_Developer is a role/job title.

Some of it’s roles are:

accessAccountMGT

accessADRCompliance

accessBillBatch

accessBillView

Those could be shared by other job titles. For example JT_ClaimsAsst could have them. Say I need to list all the users in accessAccountMGT. I don’t think the example you gave me is recursing though all the roles and bringing back the users. The CTE example is doing that recursion. I think it’s basically flipping the example I posted around but I don’t know how.