SQL Server User roles that have roles inside them

Question

I know the title might be a bit confusing, but that was the best I could came up with. So here is my question: I have a DB that has many roles, some of these roles have as a role member other roles, so when a users is added into a role it will automatically add that users to other roles as well.

For exameple in my db if I run the SQL :

exec sp_helprolemember op_ConfigDefault -- this will give me all the members of the op_ConfigDefault role. Inside these role some of the members are infact roles. I made some print screen to be more clear

Here in the blue squere I have the members with are roles and in the red square are normal users.

Here you can see those "member" from above are indeed roles.

My question now is, is there a way I could build a T-SQL so I can get only the Members from a role that are also roles?

Answer 1

I've mananaged to find the solution

SELECT DbRole = g.NAME,
    MemberName = u.NAME,
    MemberSID = u.sid
FROM sys.database_principals u,
    sys.database_principals g,
    sys.database_role_members m
WHERE g.NAME = /* add role name here*/
    AND g.principal_id = m.role_principal_id
    AND u.principal_id = m.member_principal_id
    AND u.NAME IN (
        SELECT NAME
        FROM sys.database_principals
        WHERE type = 'R'
        )
ORDER BY 1, 2