Still more ASP.NET membership tweaks!
The role manager in ASP.NET Membership provides a mechanism to list all the users in the ASP.NET Membership Database for a specific role.
This method returns a string array of usernames that’s a member of that role. Unfortunately, this list returns all the users even if they are approved or not, locked out or not. If you are working on an application that requires to filter out users in the role that’s locked out or isn’t approved yet, there’s no other mechanism on the built-in methods. There’s no overload to the method provided out of the box.
Luckily, as mentioned in the previous blog about ASP.NET membership, the database is filled with various stored procedures that you can use (or even pattern after) for various usage. I tweaked one of the stored procedures and came up with a stored procedure that gets all the approved users in the ASP.NET membership database.
CREATE PROCEDURE dbo.Membership_GetActiveUsersInRoles @RoleName nvarchar(256) AS BEGIN DECLARE @RoleId uniqueidentifier SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LOWER(@RoleName) = LoweredRoleName SELECT u.UserName, u.UserId FROM dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur, dbo.aspnet_Membership m WHERE u.UserId = m.UserId AND u.UserId = ur.UserId AND @RoleId = ur.RoleId and m.IsApproved = 'true' -- You can also specify m.IsLockedOut = 'false' for other purposes END
Just copy the said code above and create it in your ASP.NET Membership database (either that ASPNETDB.MDF or an ASP.NET membership configured database) and run it.Happy Coding!