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 = @EmailDatabase 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.
The primary key in this table is the RoleID identity field.