Check users in a security group in SQL Server

Question

In the Security/Users folder in my database, I have a bunch of security groups, include "MyApplication Users". I need to check if I am (or another user is) in this group, but I have no idea how to query for it or where I could see this information. I tried looking in the properties, but couldn't find anything. Any ideas?

Answer 1

Checking yourself or the current user:

SELECT IS_MEMBER('[group or role]')

A result of 1 = yes,0 = no, and null = the group or role queried is not valid.

To get a list of the users, try xp_logininfo if extended procs are enabled and the group in question is a windows group :

EXEC master..xp_logininfo 
@acctname = '[group]',
@option = 'members'

Answer 2

For a quick view of which groups / roles the current user is a member of;

select
      [principal_id]
    , [name]
    , [type_desc]
    , is_member(name) as [is_member]
from [sys].[database_principals]
where [type] in ('R','G')
order by [is_member] desc,[type],[name]