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

5 thoughts on “Searching Approved Users in Roles in ASP.NET Membership

  1. 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. 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?

  3. Thanks Dude,

    Updated it a bit to include options and paging, thought I’d share it with ya.

    CREATE PROCEDURE [dbo].[usp_Membership_GetUsers]

    @RoleName nvarchar(256) = NULL,
    @IsApproved bit = NULL,
    @IsLockedOut bit = NULL,
    @StartRow int = 1,
    @PageSize int = 10

    AS
    BEGIN

    SET NOCOUNT ON;

    DECLARE @EndRow int
    SET @EndRow = @StartRow + @PageSize

    SELECT * FROM
    (
    SELECT
    ROW_NUMBER() OVER(ORDER BY [CreateDate] DESC) As RowNum
    ,u.[UserName]
    ,u.[UserId]
    ,u.[LastActivityDate]
    ,m.[IsApproved]
    ,m.[IsLockedOut]
    ,m.[Email]
    FROM
    [dbo].[aspnet_Users] u
    ,[dbo].[aspnet_UsersInRoles] ur
    ,[dbo].[aspnet_Membership] m
    WHERE
    u.[UserId] = m.[UserId] AND
    u.[UserId] = ur.[UserId] AND
    — ====================
    — filter on role?
    — ====================
    (@RoleName IS NULL OR
    (@RoleName IS NOT NULL AND ur.[RoleId] IN (SELECT [RoleId]
    FROM [dbo].[aspnet_Roles]
    WHERE LOWER(@RoleName) = [LoweredRoleName]))) AND
    — ====================
    — filter on approved?
    — ====================
    (@IsApproved IS NULL OR
    (@IsApproved IS NOT NULL AND m.[IsApproved] = @IsApproved)) AND
    — ====================
    — filter on locked out?
    — ====================
    (@IsLockedOut IS NULL OR
    (@IsLockedOut IS NOT NULL AND m.[IsLockedOut] = @IsLockedOut))
    ) T
    WHERE
    RowNum BETWEEN @StartRow AND @EndRow

  4. Undeniably believe that which you said. Your favorite justification appeared to be on the internet
    the simplest thing to be aware of. I say to you, I definitely get annoyed while people
    consider worries that they just do not know about. You managed to hit the nail
    upon the top as well as defined out the whole thing without having side-effects ,
    people can take a signal. Will probably be back to get more.

    Thanks

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