By default, the membership database is setup in the App_Data folder of your application; name of the database file is ASPNETDB.MDF. It is very likely that you want this set of tables to be in your regular SQL Server database for any number of reasons:

• You want all the user information tables to be near your other (application-related) tables, so you can maintain some relationships between these tables.
• You don’t want multiple databases: one for just the user data and another for application data
• Your hosting company might not provide the ability to use SQLEXPRESS databases for security reasons

So, how would you set up the membership and roles related tables in your regular SQL Server databases?

Check out the following links for step-by-step instructions:

Scott Guthrie’s blog post on "Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005":

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

It also appears on MSDN (better formatting):

http://msdn2.microsoft.com/en-us/aa479307.aspx

In Summary:

Use aspnet_regsql.exe from command line and follow the instructions.

Following commands should work (choose the appropriate .NET Framework directory)

-Using the above utility you can either install or remove the set of database objects (tables, views, stored procedures) used for membership, roles, etc. So, don’t worry about accidentally cluttering your db -- if you installed this by mistake, you can remove them as well.

After you successfuly run the above utility, you will see the following in your database:

• 11 new tables with names prefixed with aspnet_
• 9 new views with names prefixed with vw_aspnet_
• Several new stored procedures prefixed with aspnet_

Now, how does your application know where are these membership related tables installed? Setup a connection string for "LocalSqlServer" (not the most intuitive name, but that’s what’s used by Microsoft).

Now you can use the Web Site Administration Tool to further setup the users, roles, etc.