How can I migrate users into a group

Scenario

You have a list of members that all belong to a role on your site, and you want these members to be added as members to a group.

Assumption

The following script assumes that you have your membership store in the same place as your Community Server data.

Solution

Adding users that belong to a specific role to a group can be done with the use of a sql script.  You will need to connect to your database and determine the SectionID of the group that you want to add members to.  To do this you can run the following command, replacing the name of the group with your own.

SELECT SectionID FROM cs_Sections WHERE [Name] = 'YourGroupName' AND HubSectionID = SectionID

Once you have the section ID for your group you can run the following sql.  It will add all of the users in the provided role name to the group you found with the above command.  Make sure to replace both the rolename and HubSectionID with your own.

-- MembershipType Possible Values
-- Owner   = 1
-- Manager = 2
-- Member  = 4

DECLARE @RoleName varchar(255), @HubSectionId int, @MembershipType int
SET @RoleName = 'YourRoleName'
SET @HubSectionId = 0
SET @MembershipType = 4




DECLARE @UserList cursor
SET @UserList = cursor for
    SELECT U.UserID FROM cs_Users U
    INNER JOIN aspnet_UsersInRoles UR ON UR.UserId = U.MembershipId
    INNER JOIN aspnet_Roles R ON R.RoleId = UR.RoleId AND R.RoleName = @RoleName

OPEN @UserList
FETCH NEXT FROM @UserList INTO @UserID
WHILE (@@FETCH_STATUS = 0)
BEGIN

        UPDATE cs_SectionMembers
            Set MembershipType = @MembershipType
        WHERE
            SectionID = @HubSectionId AND UserID = @UserID

      IF @@ROWCOUNT = 0
          BEGIN
            INSERT INTO cs_SectionMembers
              (SectionID, UserID, MembershipType)
            VALUES
              (@HubSectionId, @UserID, @MembershipType)
          END
    FETCH NEXT FROM @UserList INTO @UserID
END
CLOSE @UserList
DEALLOCATE @UserList