SQL Server用户角色，其中包含角色

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 :

对于我的数据库中的exameple,如果我运行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

exec sp_helprolemember op_ConfigDefault - 这将为我提供op_ConfigDefault角色的所有成员。在这些角色中,一些成员具有实际作用。我做了一些打印屏幕更清晰

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

在蓝色squere我有成员与角色,在红色方块是普通用户。

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?

我现在的问题是,有没有办法构建一个T-SQL,所以我只能从角色中获得角色的成员?

1 个解决方案

#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