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

15.5. Authorizing with Roles in the Data Layer

Because all of the user-to-role associations are stored in the database, and the SqlRoleProvider database schema includes SQL views that map to these tables, you can perform authorization checks in the database using this information. Depending on how your application is structured, you may find it to be more efficient to make a series of authorization checks in the database, as opposed to pulling information back up to the middle tier and then making a series of authorization checks using Role Manager. Older applications that have large amounts of their business logic still in stored procedures may need to keep their authorization logic in the database as well because it may be technically impossible to factor out the authorization checks to a middle tier.

As with the Membership feature, the first step you need to accomplish is the conversion of a (username, application name) pair to the GUID user identifier used in the database tables. You will want to store the result of converting an application name to a GUID identifier because you also need to convert a role name to its GUID identifier. Because role names are segmented by applications, just as usernames are partitioned by application, you will always be performing authorization checks in the context of a specific application name.

SQL Server 2000 conveniently supports user defined functions, so you can encapsulate all of this logic inside of a custom user-defined function.