"AddUserRole" Stored Procedure

This stored procedure adds a user into the specified security role. Roles are defined on a per-portal basis in the Roles table.

    
   CREATE PROCEDURE Portal_AddUserRole
   (
       @UserID int,
       @RoleID int
   )
   AS
   
   SELECT 
       *
   FROM Portal_UserRoles
   
   WHERE
       UserID=@UserID
       AND
       RoleID=@RoleID
   
   /* only insert if the record doesn't yet exist */
   IF @@Rowcount < 1
   
       INSERT INTO Portal_UserRoles
       (
           UserID,
           RoleID
       )
   
       VALUES
       (
           @UserID,
           @RoleID
       )
        

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.