CODEWITHSUNDEEP
sqlsql-servert-sqlrolesmembers
CiucaS
CiucaS

Reputation: 2128

SQL Server User roles that have roles inside them

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 enter image description here

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

enter image description here

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?

Upvotes: 2

Views: 121

Answers (1)

CiucaS
CiucaS

Reputation: 2128

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

Upvotes: 3

Related Questions