14

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?

Igor Borisenko
  • 3,806
  • 3
  • 34
  • 49
user682732
  • 171
  • 1
  • 5

2 Answers2

29

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
gplwhite
  • 291
  • 3
  • 3
-4

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'
Rob Knight
  • 1
  • 1