/*************
List out all server logins.
*************/
select 
	sp.name,
	sp.type_desc,
	sp.default_database_name,
	sl.is_policy_checked,
	sl.is_expiration_checked
from sys.server_principals sp 
	left join sys.sql_logins sl on (sp.principal_id = sl.principal_id)
where sp.type not in ('R','C')
order by name


/*************
List all server role membership
*************/
select 
	ISNULL(r.name,'No Role') [Server Role],
	u.name [Login],
	u.type_desc [User Type]
from (select name,principal_id from sys.server_principals where type = 'R') r
	right join sys.server_role_members rm 
		on (r.principal_id = rm.role_principal_id)
	right join (select name,type_desc,principal_id from sys.server_principals where type != 'R') u 
		on (rm.member_principal_id = u.principal_id)
order by r.name

/*************
List all database role membership
*************/
select 
	ISNULL(r.name,'No Role') [Database Role],
	u.name [Login],
	u.type_desc [User Type]
from (select name,principal_id
		from sys.database_principals where type = 'R') r
	right join sys.database_role_members rm 
		on (r.principal_id = rm.role_principal_id)
	right join (select name,type_desc,principal_id
		from sys.database_principals where type != 'R') u 
		on (rm.member_principal_id = u.principal_id )