Examining ASP.NET 2.0's Membership, Roles, and Profile - Part 9
By Scott Mitchell
A Multipart Series on ASP.NET's Membership, Roles, and Profile
This article is one in a series of articles on ASP.NET's membership, roles, and profile functionality.
Part 1 - learn about how the membership features
make providing user accounts on your website a breeze. This article covers the basics of membership, including why it is needed,
along with a look at the SqlMembershipProvider and the security Web controls.
Part 2 - master how to create roles and
assign users to roles. This article shows how to setup roles, using role-based authorization, and displaying output on
a page depending upon the visitor's roles.
Part 3 - see how to add the membership-related
schemas to an existing database using the ASP.NET SQL Server Registration Tool (aspnet_regsql.exe).
Part 4 - improve the login experience by
showing more informative messages for users who log on with invalid credentials; also, see how to keep a log of invalid
login attempts.
Part 5 - learn how to customize the Login control.
Adjust its appearance using properties and templates; customize the authentication logic to include a CAPTCHA.
Part 6 - capture additional user-specific
information using the Profile system. Learn about the built-in SqlProfileProvider.
Part 7 - the Membership, Roles, and Profile systems
are all build using the provider model, which allows
for their implementations to be highly customized. Learn how to create a custom Profile provider that persists user-specific
settings to XML files.
Part 8 - learn how to use the Microsoft Access-based
providers for the Membership, Roles, and Profile systems. With these providers, you can use an Access database instead of
SQL Server.
Part 9 - when working with Membership, you have the
option of using .NET's APIs or working directly with the specified provider. This article examines the pros and cons of
both approaches and examines the SqlMembershipProvider in more detail.
Part 10 - the Membership system includes features
that automatically tally the number of users logged onto the site. This article examines and enhances these features.
Part 11 - many websites require new users to verify their
email address before their account is activated. Learn how to implement such behavior using the CreateUserWizard control.
Part 12 - learn how to apply user- and role-based
authorization rules to methods and classes.
Part 13 - see how to create a login screen
that allows Admin users to log in as another user in the user database.
Part 14 - learn how to create a page that permits
users to update their security question and answer.
Part 15 - the Membership API does not provide a means to change
a user's username. But such functionality is possible by going directly to the user store, as this article illustrates.
Introduction
ASP.NET 2.0's Membership, Roles, and Profile systems were designed using the provider model, which
enables these systems to seamlessly use different implementations. ASP.NET ships with a provider for managing members and roles through SQL Server and another for using
Active Directory. It is also possible to plug in other implementations that have been built from the ground up or downloaded from other sources. For example, you can download
alternative providers from Microsoft that store membership and role information in a Microsoft Access database (see
Part 8 of this article series). Most of the articles in this series, however, have focused on using the
SQL Server provider (SqlMembershipProvider, SqlRoleProvider, and SqlProfileProvider). The SQL Server providers are typically
the provider of choice for Internet-based web applications, whereas the Active Directory providers are more commonly used in intranet scenarios.
The SQL Server providers create a number of tables, views, and stored procedures in the specified SQL Server database. Therefore when using these providers it is possible
to add, modify, or delete membership or roles or profile-related data through T-SQL statements. In this article we'll look at a common membership need - deleting users.
While users can certainly be deleted through the .NET Membership API, there are scenarios where it may be much easier to use a T-SQL script. However, bypassing the
managed APIs and working directly with the database is not without its own host of challenges. By the end of this article we'll have addressed these issues, discussed the
pros and cons of using T-SQL in lieu of the managed APIs, and have examined both the managed API methods and T-SQL commands for deleting a single user and deleting
all users. Read on to learn more!
Deciding Whether to Work with the Programmatic APIs or Whether to Work with SQL Server Directly
The SQL Server providers create the tables, views, and stored procedures needed by the Membership, Roles, and Profile systems in a specified SQL Server database.
These database objects are created automatically in the ASPNETDB.MDF database in the ~/App_Data folder when using the
ASP.NET Website Administration Tool. Alternatively, these objects can be
explicitly added via the ASP.NET SQL Server Registration Tool (aspnet_regsql.exe) (see
Part 3).
.NET's Membership, Roles, and Profile APIs offer a host of methods for programmatically creating, modifying, and deleting user accounts. However, when using the SQL Server
providers it may be tempting to modify, add, or delete user accounts directly through T-SQL commands rather than through code. These API methods call the applicable stored
procedures. Bypassing the APIs and stored procedures and modifying table data manually has the potential to lead to incorrect or corrupted data. For example, in the Membership
table each user's password is stored. If the Membership system is configured to use a hash of the password, modifying the password incorrectly (or modifying its salt) can essentially
lock out that user since their plain-text password no longer matches up to the stored password hash. In short, if you decide to work with the user information directly from T-SQL
commands, it is best to do so through the stored procedures explicitly added by the provider. Moreover, it is essential to have a solid understanding of the data model and
the stored procedure(s) you invoke.
There are several advantages to using the managed APIs as opposed to making modifications to the database directly. For one, the managed APIs are much easier to understand
than the SQL Server-based providers' stored procedures and data model. The methods and properties in the Membership, Roles, and Profile APIs are well-documented on
Microsoft's website and the property names and method names and input parameters can often be understood just by their names and through the short IntelliSense description.
The SQL Server stored procedures, on the other hand, are far less documented and require parsing the contents to fully understand what's happening.
The downside of the managed APIs is that you must write code to perform the action and this code must somehow be executed. Typically, such code will be placed in a web page
in some Administration section on a site, requiring a user to logon to the site, visit the Administration section, and execute the desired command (such as deleting a particular user
or removing all users from the database). The upside of issuing T-SQL commands is that they can more easily be invoked. Imagine that you have a test server with some artificially created
accounts. When rolling out a new version of the software you may want to remove all account data and recreate new test accounts. This can be accomplished by executing a T-SQL
script with the appropriate commands to remove the current users and add in the test users.
The remainder of this article looks at how to delete a single user and delete all users using both the managed APIs and T-SQL commands.
Deleting a Single User
To delete a single user using the managed APIs, use the Membership.DeleteUser(username[, deleteAllData]
method. This method takes, at minimum, a single string input - the username of the user to delete. It can also accept an optional Boolean parameter that indicates whether or not to delete
all related user information, such as their role information, profile data, and so on. If you do not explicitly provide this Boolean parameter, a value of True is used. For the SQL Server provider, the
Membership.DeleteUser method invokes the stored procedure aspnet_Users_DeleteUser, which expects the following parameters:
@ApplicationName - the application which the specified user is found and is to be deleted from. As discussed in Part
1 of this article series, the membership system is partitioned into applications by application names. This allows for multiple web applications to use a common member store.
@UserName - the name of the user to delete.
@TablesToDeleteFrom - an integer field that specifies what membership-related tables to delete data from. This parameter serves as a bit field that indicates
what subset of tables to remove membership data from. If you pass in a value of 1, data is only deleted from the aspnet_Membership table; the user is not removed from the
aspnet_Users table. Not surprisingly, if you call Membership.DeleteUser(username, False), a value of 1 is passed to this parameter. Other
values for this parameter can specify that the stored procedure should delete the record from other combinations of tables. Passing in a value of 15 (which is what is passed in when calling
Membership.DeleteUser(username) or Membership.DeleteUser(username, True), deletes all user related records from the user account-related
tables, including aspnet_Users. In short, passing in a value of 15 deletes the user entirely from the database.
@NumTablesDeletedFrom - an output parameter that returns the number of tables where data was deleted from.
The demo available for download at the end of this article includes a web page that lets the visitor view all of the users in a drop-down list. They can pick a user and then opt to delete him by
clicking a button. Doing so deletes the user by calling Membership.DeleteUser(username).
To delete a particular user through T-SQL, simply call the aspnet_Users_DeleteUser stored procedure passing in the appropriate parameters.
DECLARE @NumTablesDeletedFrom int
EXEC aspnet_Users_DeleteUser applicationName, userName, 15, @NumTablesDeletedFrom OUTPUT
PRINT 'Number of tables deleted from: ' + @NumTablesDeletedFrom
Deleting All Users
The Membership API does not include a Membership.DeleteAllUsers() method. Nor is there a stored procedure in the SQL Server provider that deletes all users. But with
a little elbow grease we can implement this functionality ourselves. The Membership API does provide a method to get all users in the system -
Membership.GetAllUsers. We can use this method to get a list
of users back and then loop through the users one at a time calling the Membership.DeleteUser(username) method.
' Visual Basic
'Enumerate each user and delete him!
For Each usr As MembershipUser In Membership.GetAllUsers()
Membership.DeleteUser(usr.UserName)
Next
// C#
foreach (MembershipUser usr in Membership.GetAllUsers())
Membership.DeleteUser(usr.UserName);
A similar approach can be used to delete all users directly through T-SQL commands. You can create a CURSOR that enumerates the set of users found in the aspnet_Users table
and then for each record call the aspnet_Users_DeleteUser stored procedure. For more on CURSORs see Using
SQL Server Cursors.
There's an easier approach, however: simply delete all of the records from all of the corresponding tables. The only challenge here is that there are foreign key constraints between related tables so
you have to be certain to delete the data from the "child" tables before deleting the data from the "parent" tables. The following string of DELETE commands deletes the data in the correct
order:
DELETE FROM dbo.aspnet_Membership
DELETE FROM dbo.aspnet_UsersInRoles
DELETE FROM dbo.aspnet_Profile
DELETE FROM dbo.aspnet_PersonalizationPerUser
DELETE FROM aspnet_Users
As a final option, you could remove all users by obliterating the member store by reinstalling the SQL Server provider database objects via aspnet_regsql.exe.
A Multipart Series on ASP.NET's Membership, Roles, and Profile
This article is one in a series of articles on ASP.NET's membership, roles, and profile functionality.
Part 1 - learn about how the membership features
make providing user accounts on your website a breeze. This article covers the basics of membership, including why it is needed,
along with a look at the SqlMembershipProvider and the security Web controls.
Part 2 - master how to create roles and
assign users to roles. This article shows how to setup roles, using role-based authorization, and displaying output on
a page depending upon the visitor's roles.
Part 3 - see how to add the membership-related
schemas to an existing database using the ASP.NET SQL Server Registration Tool (aspnet_regsql.exe).
Part 4 - improve the login experience by
showing more informative messages for users who log on with invalid credentials; also, see how to keep a log of invalid
login attempts.
Part 5 - learn how to customize the Login control.
Adjust its appearance using properties and templates; customize the authentication logic to include a CAPTCHA.
Part 6 - capture additional user-specific
information using the Profile system. Learn about the built-in SqlProfileProvider.
Part 7 - the Membership, Roles, and Profile systems
are all build using the provider model, which allows
for their implementations to be highly customized. Learn how to create a custom Profile provider that persists user-specific
settings to XML files.
Part 8 - learn how to use the Microsoft Access-based
providers for the Membership, Roles, and Profile systems. With these providers, you can use an Access database instead of
SQL Server.
Part 9 - when working with Membership, you have the
option of using .NET's APIs or working directly with the specified provider. This article examines the pros and cons of
both approaches and examines the SqlMembershipProvider in more detail.
Part 10 - the Membership system includes features
that automatically tally the number of users logged onto the site. This article examines and enhances these features.
Part 11 - many websites require new users to verify their
email address before their account is activated. Learn how to implement such behavior using the CreateUserWizard control.
Part 12 - learn how to apply user- and role-based
authorization rules to methods and classes.
Part 13 - see how to create a login screen
that allows Admin users to log in as another user in the user database.
Part 14 - learn how to create a page that permits
users to update their security question and answer.
Part 15 - the Membership API does not provide a means to change
a user's username. But such functionality is possible by going directly to the user store, as this article illustrates.
