Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to list role members in SQL Server 2008 R2

Tags:

I'm using the following T-SQL to obtain role members from my SQL Server 2008 R2 database:

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) order by rp.name 

When I examine the output I notice that the only role members listed for db_datareader are db roles - no user members of db_datareader are listed in the query.

Why is that? How can I also list the user members of my db roles?

I guess I should also ask whether the table sys.database_role_members actually contains all members of a role?

like image 920
endurium Avatar asked Jan 07 '14 12:01

endurium


People also ask

How do I get a list of user roles in SQL Server?

To find all the role assignments to users in SQL Server database, you can use the following query. SELECT r.name role_principal_name, m.name AS member_principal_name FROM sys. database_role_members rm JOIN sys. database_principals r ON rm.

How do I find database roles?

In SSMS right click on database -> properties -> Permissions and see Explicit tab for a user X . I can see Permissions not association between role and the user. The same is for role I'm interested in, I see only permissions for role.

How can add database role membership in SQL Server?

Only use sp_addrolemember to add a member to a database role. To add a member to a server role, use sp_addsrvrolemember (Transact-SQL).

How do I get list of logins and permissions in SQL Server?

SQL Server includes a very useful system function sys. fn_my_permissions to list all the permissions of a particular principal (user or login) and this system function will help you list all permissions of a principal on a specific database object (securable).


2 Answers

I've worked out what's going on.

When I queried out the role members I was comparing the output with what SSMS listed as role members in the role's properties dialog - this included users as well as roles, but the users weren't being listed by the query as listed in my question. I turns out that when listing role members, SSMS expands members that are roles to display the members of those roles.

The following query replicates the way in which SSMS lists role members:

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_userl 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) order by rp.name 

The above query uses a recursive CTE to expand a role into it's user members.

like image 111
endurium Avatar answered Oct 22 '22 00:10

endurium


Here is another way

SELECT dp.name , us.name   FROM sys.sysusers us right  JOIN  sys.database_role_members rm ON us.uid = rm.member_principal_id JOIN sys.database_principals dp ON rm.role_principal_id =  dp.principal_id 
like image 38
bigRon97 Avatar answered Oct 22 '22 02:10

bigRon97