"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 GetRolesByUser
    (
        @Email         nvarchar(100)
    )
    AS

    SELECT  
        Roles.RoleName,
        Roles.RoleID

    FROM
        UserRoles
    INNER JOIN 
        Users ON UserRoles.UserID = Users.UserID
    INNER JOIN 
        Roles ON UserRoles.RoleID = Roles.RoleID

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