列出 SQL Server 中的用户及其角色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3609318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Listing users and their roles in SQL Server
提问by balaji
I want to get a list of all the users in the SQL server database and their roles. What I'm trying to do is to find out if certain users have privileges to more than one database. Is there a query which can do this directly?
我想获取 SQL Server 数据库中所有用户及其角色的列表。我想要做的是找出某些用户是否拥有多个数据库的权限。是否有可以直接执行此操作的查询？
回答by kbrimington
I think you'll find this resource helpful:
我想你会发现这个资源很有帮助：
From the article:
从文章：
select dp.NAME AS principal_name,
dp.type_desc AS principal_type_desc,
o.NAME AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o
on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
回答by Venkataraman R
You can use the below command to find users and corresponding role in each database:
您可以使用以下命令在每个数据库中查找用户和相应角色：
exec sp_MSForeachDB @command1='SELECT db_name(db_id('' ? ''))
,user_name(DRM.member_principal_id) [DatabaseUser]
,user_name(DRM.role_principal_id) [DatabaseRole]
FROM sys.database_role_members DRM
INNER JOIN sys.database_principals DP
ON DRM.member_principal_id = DP.principal_id
INNER JOIN sys.database_principals dpr
ON drm.role_principal_id = dpr.principal_id
WHERE DRM.member_principal_id > 1
AND dpr.type IN ('' R '', '' A '')'
