SQLGear

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