users assigned a sql azure role

Question

I am trying to make sure that all users have been assigned a particular role. Is there any view or SQL query for getting this information?

Marketable

Answer 1

The views have changed names but the following should work against SQL Azure

select m.name as Member, r.name as Role
from sys.database_role_members
inner join sys.database_principals m on sys.database_role_members.member_principal_id = m.principal_id
inner join sys.database_principals r on sys.database_role_members.role_principal_id = r.principal_id

Legnica

This answer is exactly what is required in SQL Azure (for which the question is tagged). – Mehalick

Answer 2

I believe the query listed below should list the users and roles of a single database

select b.name as USERName, c.name as RoleName  from dbo.sysmembers a   join dbo.sysusers b   on a.memberuid = b.uid join dbo.sysusers c  on a.groupuid = c.uid

Or for all DBs on a SQL instance you can use sp_MSForEachDB

Exec dbo.sp_MSForEachDB 'select ''?'' as DB, b.name as USERName, c.name as RoleName  from dbo.sysmembers a   join dbo.sysusers b   on a.memberuid = b.uid join dbo.sysusers c  on a.groupuid = c.uid'

Downall