Question: 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?

Answer: 
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

```
You can use the below command to find users and corresponding role in each database:

```sql
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 '')'

```