Article C0013 C# .NET
SQL Server as Role Provider

When you have implemented SQL Server as Authentication Provider as descibed in article C0012, than you can extend that implementation with authorization using Role management.

The first step to take is to add Role Management within the <web.config>-file. Under the <authorization>-section the following section must be added:

<roleManager enabled="true" defaultProvider="SqlRoleManager">
    <providers>
        <add name="SqlRoleManager"
            type="System.Web.Security.SqlRoleProvider,
            System.Web, Version=2.0.3600.0, Culture=neutral,
            PublicKeyToken=b03f5f7f11d50a3a"
            connectionStringName="SqlConnection"
            applicationName="Former" />
        </providers>
</roleManager>
So we are going to re-use the connection string connectionStringName="SqlConnection" which we already defined earlier within the web-configurationfile.

Once again we use the PublicKeyToken to avoid the ‘default provider type could not be determined to check it is a trusted provider’-error. (We already used it in the membership-section).

Untrusted provider
The next steps are only required to show you how to use the role provider within the .ASPX and code-behind file. First, add a CheckBoxList-control from the toolbox or add one manually within the .ASPX-file like this:

    <asp:CheckBoxList ID="cbList" runat="server">
    </asp:CheckBoxList>
We are going to use it to add the available roles and will check the checkbox if the logged on user is a member of the role. Next add the web-security reference:

    using System.Web.Security;
Now modify the Page_Load() method by adding code to populate the CheckBoxList and set the role(s) which the user is in:

// Populate list:
foreach (string role in Roles.GetAllRoles())
{
    cbList.Items.Add(new ListItem(role));
}
// Check membership:
foreach (ListItem item in cbList.Items)
{
    if (Roles.IsUserInRole(item.Text)) item.Selected = true;
}
Next build and publish, but do not execute the code. We have to add some roles first using the Internet Information Services Manager. So start the IIS Manager, select the application and doubleclick on the ‘.NET Roles’-icon. Now, from the Action-menu select Add and the ‘Add .NET Role’-role dialog will pop-up.

Add .NET Role
Within our example, I've added two roles, one called Boys and one called Girls. Next reselect yhe application within the IIS-Manager and doubleclick on the ‘.NET Users’-icon. Select one user from the list and notice that the Alerts area shows some information:

No membership
Next edit the selected user and assign him/her in one of the two roles:

Assign role
I've selected the role Boys for the account of Vincent. Now go back to the ‘.NET Roles’-menu and notice that the membership counter has been increased:

Role counter
Finally use your browser and try the application. After you have logged on; your identity, available roles and membership information will be shown.

You can download the source from this article right here.

~Edward