Searching Users with ASP.NET Membership Database

In my previous post, we have successfully bind the Membership.GetAllUsers() method to a databound control such as the GridView to enable paging and sorting. This time, I am building a mechanism that will allow me to search for users in the ASP.NET Membership database. By default, ASP.NET Membership supports searching by specific matches: email (Membership.FindUsersByEmail()), user id (aka provider user key), and by username (Membership.FindUsersByName()). There’s no built-in mechanism wherein you can query for users that contain specific characters — say you want to get users that starts with a specific letter or contains certain characters.

Examining closely the ASP.NET Membership database (either created by the WSAT or configured using the aspnet_regsql.exe), it has stored procedures that performs various queries to the ASP.NET membership. I tweaked one query to get the result that I wanted and stored into a new stored procedure and named it “Membership_QueryUsersByLetter”. This stored procedure should reside on the same database of your ASP.NET Membership.

CREATE PROCEDURE [dbo].[Membership_QueryUsersByLetter]
    @SearchKey nvarchar(256)
    SELECT u.UserName, m.Email, m.PasswordQuestion,
	m.Comment, m.IsApproved,
	u.UserId, m.IsLockedOut,
	dbo.aspnet_Membership m, dbo.aspnet_Users u
	u.UserId = m.UserId AND u.UserName LIKE '%' + @SearchKey + '%'

This query assumes that the ASP.NET membership database is configured for one application only. A sample application that you can use for this one can be downloaded here. Rename the file (searchusers.odt) to a zip extension file to extract files. Note however that you need to modify the connection string to match your current machine configuration to point to an ASP.NET membership database (or ASP.NET Membership configured database).


Leave a Reply

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

You are commenting using your 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