what is the query statement to get list of all Users within a specific DB role?

Question

Hello, what is the query statement to get list of all Users within a specific DB role?

Thanks in advance.

Answer 1

within a specific DB role?

For database roles & members you have to use the database DMV instead of server DMV's =>

select *
from sys.database_principals as roles
     inner join
     sys.database_role_members as drm
         on roles.principal_id = drm.role_principal_id
     inner join
     sys.database_principals as mem
         on drm.member_principal_id = mem.principal_id
where roles.type = 'R'

Answer 2

Hi techresearch,

SELECT m.name as Principal, r.name as Role
FROM
	master.sys.server_role_members rm 
		INNER JOIN master.sys.server_principals r 
			ON r.principal_id = rm.role_principal_id AND r.type = 'R'
		INNER JOIN master.sys.server_principals m
			ON m.principal_id = rm.member_principal_id
WHERE m.name = 'testuser'

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!