List role membership

Credits Greyhood

Ever wanted to check if a database user or custom database role belongs to what other database roles or has what access to the database. Following is the query:

DECLARE @Rolename CHAR(15)

SET @Rolename = 'Custom DB Role'

--//Set the variable value in above line to the database role or user for which membership needs to be checked
SELECT @@ServerName [Server]
 ,DB_NAME() AS [Database]
 ,@Rolename [DBRole/DBRole]
 ,CASE IS_ROLEMEMBER(NAME, @Rolename)
  WHEN 1
   THEN 'Is member of ' + NAME
  WHEN 0
   THEN 'Is not member of ' + NAME
  END AS [Membership]
FROM sysUsers
WHERE NAME <> @Rolename
 AND issqlrole = 1
 AND IS_ROLEMEMBER(NAME, @Rolename) = 1

--//comment the above line if you also want to see what the database role (or user) is not member of