Professional ASP.NET 3.5 Security, Membership, and Role Management with C# and VB

15.1. SqlRoleProvider Database Schema

The database schema contains tables, views, and stored procedures used by the provider. As with the Membership feature, SqlRoleProvider's schema integrates with the common set of tables covered in Chapter 12. This allows you to use SqlMembershipProvider for authentication and then use SqlRoleProvider to associate one or more roles with the users already registered in the Membership feature. Keying off of the common tables also allows SqlRoleProvider to be used in conjunction with the other SQL-based providers (SqlProfileProvider and SqlPersonalizationProvider) supplied by ASP.NET. However, there is no requirement that SqlRoleProvider be used in conjunction with the Membership feature. The integration with the common provider schema is nice if you want to leverage it, but you can also use Role Manager and SqlRoleProvider as a standalone authorization feature. You will actually see how this works later on in the chapter, where using SqlRoleProvider with Windows authentication is described.

Because the concept of a role in Role Manager is very simple, and because Role Manager also does not support the concept of nested roles, the database tables for the SqlRoleProvider are also very simple. The first table in the database schema is the aspnet_Roles table shown in the following code:

CREATE TABLE dbo.aspnet_Roles ( ApplicationId uniqueidentifier NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId), RoleId uniqueidentifier PRIMARY ...