SQL Server: query database user roles for all databases in server
- by atricapilla
I would like to make a query for database user roles for all databases in my sql server instance. I modified a query from sp_helpuser:
select u.name
,case when (r.principal_id is null) then 'public' else r.name end
,l.default_database_name
,u.default_schema_name
,u.principal_id
from sys.database_principals u
left join (sys.database_role_members m join sys.database_principals r on m.role_principal_id = r.principal_id)
on m.member_principal_id = u.principal_id
left join sys.server_principals l on u.sid = l.sid
where u.type <> 'R'
How can I modify this to query from all databases? What is the link between sys.databases and sys.database_principals?
