Tuesday, November 18, 2014

How to list role members in SQL Server

 

Below is the query to list the users in a specific role.  if you are looking for the users who can manage the sql server agent jobs, use the filter ( Where rp.name = ‘SQLAgentRole’)  and run this under ‘msdb’ database. otherwise remove the filter

 

;WITH RoleMembers (member_principal_id, role_principal_id)
AS
(
SELECT
rm1.member_principal_id,
rm1.role_principal_id
FROM sys.database_role_members rm1 (NOLOCK)
UNION ALL
SELECT
d.member_principal_id,
rm.role_principal_id
FROM sys.database_role_members rm (NOLOCK)
INNER JOIN RoleMembers AS d
ON rm.member_principal_id = d.role_principal_id
)
select distinct rp.name as database_role, mp.name as database_user
from RoleMembers 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)
WHERE rp.name = 'SQLAgentUserRole'

0 comments:

Post a Comment