Как перечислять роли в SQL Server 2008 R2

Я использую следующий T-SQL для получения членов роли из базы данных SQL Server 2008 R2:

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 

Когда я просматриваю вывод, я замечаю, что единственными членами роли, перечисленными для db_datareader являются роли db – в db_datareader не указаны члены db_datareader .

Почему это? Как я могу также перечислить пользователей моих ролей db?

Думаю, мне также следует спросить, действительно ли таблица sys.database_role_members содержит всех членов роли?

Я выяснил, что происходит.

Когда я запросил членов роли, я сравнивал вывод с тем, какие SSMS, перечисленные как члены роли в диалоговом окне свойств роли, включали в себя как пользователей, так и роли, но пользователи не указывали запрос, как указано в моем вопросе. Я выясняю, что при перечислении членов роли SSMS расширяет члены, которые являются ролями для отображения членов этих ролей.

Следующий запрос реплицирует способ, которым SSMS перечисляет члены роли:

 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 

В приведенном выше запросе используется рекурсивный CTE для расширения роли в ее пользовательских членах.

Вот еще один способ

 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 

Попробуй это

 ;with ServerPermsAndRoles as ( select spr.name as principal_name, spr.type_desc as principal_type, spm.permission_name collate SQL_Latin1_General_CP1_CI_AS as security_entity, 'permission' as security_type, spm.state_desc from sys.server_principals spr inner join sys.server_permissions spm on spr.principal_id = spm.grantee_principal_id where spr.type in ('s', 'u') union all select sp.name as principal_name, sp.type_desc as principal_type, spr.name as security_entity, 'role membership' as security_type, null as state_desc from sys.server_principals sp inner join sys.server_role_members srm on sp.principal_id = srm.member_principal_id inner join sys.server_principals spr on srm.role_principal_id = spr.principal_id where sp.type in ('s', 'u') ) select * from ServerPermsAndRoles order by principal_name 

(Или)

 SELECT p.name, o.name, d.* FROM sys.database_principals AS p JOIN sys.database_permissions AS d ON d.grantee_principal_id = p.principal_id JOIN sys.objects AS o ON o.object_id = d.major_id