Check users in a security group in SQL Server

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

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'