SELECT	dbo.sysusers.uid, dbo.sysusers.name,IS_MEMBER(dbo.sysusers.name) as ismemeber,
*
FROM	dbo.sysusers 
WHERE dbo.sysusers.issqlrole = 1 
AND IS_MEMBER(dbo.sysusers.name)=1

select roles.*
from sys.database_principals principals
join sys.database_role_members membership on membership.member_principal_id=principals.principal_id
join sys.database_principals roles on roles.principal_id=membership.role_principal_id
where principals.type='U' and principals.name='DOMAIN\Login'

exec sp_helplogins 'DOMAIN\Login'

USE master
GO
select @@version
go
CREATE LOGIN [test_login] WITH PASSWORD=N'12345aA',
	DEFAULT_DATABASE=[master],
	DEFAULT_LANGUAGE=[us_english],
	CHECK_EXPIRATION=OFF,
	CHECK_POLICY=OFF
GO
use [stuff]
go
create user [test_login] for login [test_login]
go
create role [test_role];
exec sp_addrolemember 'test_role', 'test_login';
go
print('without sysadmin:');
select roles.name
from sys.database_principals principals
join sys.database_role_members membership on membership.member_principal_id=principals.principal_id
join sys.database_principals roles on roles.principal_id=membership.role_principal_id
where principals.type='S' and principals.name='test_login';
go
exec sp_addsrvrolemember 'test_login', 'sysadmin';  
go
print('with sysadmin:');
select roles.name
from sys.database_principals principals
join sys.database_role_members membership on membership.member_principal_id=principals.principal_id
join sys.database_principals roles on roles.principal_id=membership.role_principal_id
where principals.type='S' and principals.name='test_login';
go
exec sp_droprolemember 'test_role', 'test_login';
drop role [test_role];
drop user [test_login];
use master
go
drop login [test_login];
go

----------------------------------------------------------------------------------
Microsoft SQL Server 2014 (SP2-CU12) (KB4130489) - 12.0.5589.7 (X64) 
	Jun  9 2018 11:17:15 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


(1 row(s) affected)

without sysadmin:
name
-----------------
test_role

(1 row(s) affected)

with sysadmin:
name
-----------------
test_role

(1 row(s) affected)

select roles.*
from sys.database_principals principals
join sys.database_role_members membership on membership.member_principal_id=principals.principal_id
join sys.database_principals roles on roles.principal_id=membership.role_principal_id
where principals.type='U' and principals.name=system_user

exec sp_helprolemember