SQL Server security is very complex
1.Athentification mode Windows//Windows/SQL.
2.Login at server level, it can belong to predefined server group.
If login is imported from Windows,it can be in multiple login groups.
Stored in master.dbo.syslogins (view on master.dbo.sysxlogins)
with denormalized server group membership info.
3.User at database level=mapped server login.
Stored in dbo.sysusers ((uid <> gid and altuid=0) or [password] is not null)
together with database roles ( uid = gid or (altuid=1 and [password] is null) ),
in gid there is only one group. All membership info stored in dbo.sysmembers link table.
4.Every user is member of public role.
5.Role can be owned.
6.Roles can be chained (40 levels tested). Autochecked for circular relationship.
7.Application roles (with password, cannot have members) are similar to users.
--For first level membership:
from dbo.sysusers su
left join dbo.sysmembers sm
where (su.uid <> su.gid and su.altuid=0) or su.[password] is not null