You can use IS_MEMBER() to determine the current users inclusion in a particular group. There are also a series of dynamic management views (DMV) that access security. Try sys.database_role_members and sys.database_principals to see the list of users and their association within a role.
Something like this, as a start:
SELECT p.NAME
,m.NAME
FROM sys.database_role_members rm
JOIN sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning