Searching Approved Users in Roles in ASP.NET Membership

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.


Roles.GetUsersInRoles("rolename");

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.roles.png

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!

Advertisements

2 Responses to “Searching Approved Users in Roles in ASP.NET Membership”


  1. 1 Jon Limjap February 14, 2008 at 5:06 pm

    Patrick,

    Why don’t you just use an extension method for this?

    Or, wrap it with a method that converts the list to List, then use the .Remove(Predicate) + anonymous method?

  2. 2 superpatrick February 14, 2008 at 5:11 pm

    I am not sure if I got your question correctly but from what I see on the situation, the method alone (Roles.GetUsersInRole()) returns only a string[] of names, which is unlike the GetAllUsers() method (returns a MembershipUserCollection) that include the other fields wherein you can filter the output. Did my answer corresponds to your question?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Blog Stats

  • 3,922 hits
February 2008
S M T W T F S
« Jan   Mar »
 12
3456789
10111213141516
17181920212223
242526272829  

%d bloggers like this: