在SQL Server中列出用户及其角色

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数据库中所有用户及其角色的列表。我想要做的是找出某些用户是否拥有多个数据库的权限。有查询可以直接执行此操作吗？

1 个解决方案

#1

I think you'll find this resource helpful:

我想你会发现这个资源很有帮助：

http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions.aspx

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

#1