ASP.NET 3.5 Enterprise Application Development with Visual Studio® 2008: Problem - Design - Solution

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:

CREATE PROCEDURE ENTRoleCapabilitySelectByENTRoleId
(
  @ENTRoleId int
)
AS
  SET NOCOUNT ON

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

  RETURN

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, ...