-- SERVER ROLES
select p.name, p.type_desc, pp.name, pp.type_desc
from sys.server_role_members roles
join sys.server_principals p on roles.member_principal_id =
p.principal_id
join sys.server_principals pp on roles.role_principal_id = pp.principal_id
-- DATABASE ROLES
SELECT
p.name, p.type_desc,
pp.name, pp.type_desc, pp.is_fixed_role
FROM sys.database_role_members
roles
JOIN sys.database_principals p ON roles.member_principal_id =
p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
order by p.name, pp.name
-- DATABASE PERMISSIONS SPECIAL
SELECT
dp.class_desc, dp.permission_name, dp.state_desc,
ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name
FROM sys.database_permissions
dp
JOIN sys.database_principals grantee on dp.grantee_principal_id =
grantee.principal_id
JOIN sys.database_principals grantor on dp.grantor_principal_id =
grantor.principal_id
