How to find which FBA users are in FBA roles

Question

We are using FBA accounts to grant external access to non staff in SP2010 Enterprise.

I need to find a way of discovering the memberships of the FBA roles so I can audit who has access to our sites.

I'm currently looking at running an SQL query against the FBA database but thought there may be another way.

Answer 1

You can try SharePoint 2010 FBA Pack. Hope it may help.

Project Description reads

A forms based authentication pack for SharePoint 2010. It includes web parts for registering users, changing passwords and password recovery. It includes tools for managing users and roles and for approving registrations.

Answer 2

Running this query does the trick however I am sure there are superior solutions out there:

SELECT ROLE.RoleName, USERS.UserName, MEMBER.Email
FROM aspnet_Users AS USERS
INNER JOIN aspnet_UsersInRoles AS USERSINROLE
ON USERS.UserId = USERSINROLE.UserId
INNER JOIN aspnet_Roles AS ROLE
ON UsersInRole.RoleId = Role.RoleId
INNER JOIN aspnet_Membership AS MEMBER
ON USERS.UserId = MEMBER.UserId
ORDER BY ROLE.RoleName