6.3. Solution

The first step is to create the tables and stored procedures that the role-based security model needs, which are in a script file (available on the Wrox website) called Chapter6RoleBasedSecurity.sql. Run this script against the HRPaidTimeOff database. This script does not drop any objects, so if you have been following along in the previous chapters and creating the tables and stored procedures, you'll have to drop them manually. The script also adds some seed users in the system, which you'll need to change to match your domain and the user names on your network or local PC. The stored procedures that end with Insert, Update, Delete, SelectAll, and SelectById follow the same pattern defined in Chapter 2, so I won't review them here. Also included are a few nonstandard procedures, which are defined next.

The ENTRoleCapabilitySelectByENTRoleId stored procedure selects all the records from the ENTRoleCapability junction table for a specific role:

  @ENTRoleId int

  SELECT ENTRoleCapabilityId, ENTRoleId, ENTCapabilityId, AccessFlag, InsertDate,
         UpdateDate, UpdateENTUserAccountId, Version
    FROM ENTRoleCapability
   WHERE ENTRoleId = @ENTRoleId


The ENTRoleSelectByENTUserAccountId stored procedure selects all the roles associated with a user:

CREATE PROCEDURE ENTRoleSelectByENTUserAccountId ( @ENTUserAccountId int ) AS SET NOCOUNT ON SELECT ENTRole.ENTRoleId, RoleName, ENTRole.InsertDate, ...

Get ASP.NET 3.5 Enterprise Application Development with Visual Studio® 2008: Problem - Design - Solution now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.