"GetRolesByUser" Stored Procedure

Description:

This stored procedure returns all of the portal security roles to which the specified user belongs. The input parameter is the user's Email address.

Definition:

    
   CREATE PROCEDURE Portal_GetRolesByUser
   (
       @Email         nvarchar(100)
   )
   AS
   
   SELECT  
       Portal_Roles.RoleName,
       Portal_Roles.RoleID
   
   FROM Portal_UserRoles
     INNER JOIN 
       Portal_Users ON Portal_UserRoles.UserID = Portal_Users.UserID
     INNER JOIN 
       Portal_Roles ON Portal_UserRoles.RoleID = Portal_Roles.RoleID
   
   WHERE   
       Portal_Users.Email = @Email

Database Tables Used:

UserRoles: The UserRoles table provides a many-to-many connection between portal security roles (defined in the Roles table) and users (defined in the Users table). Using the UserRoles table, each user may belong to multiple roles and and each role may have multiple users as members.

The UserRoles table has no primary key.

Users: Each record in the Users table is a unique user identity. The primary key in this table is the UserID identity field.

Roles: Each record in the Roles table defines a unique role in the selected portal. Users are added to a role via the UserRoles lookup value table.

The primary key in this table is the RoleID identity field.