Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

mass assign roles to users?

I have converted my old forum. All my members now have unassigned roles because there is no specified "member" role at my old forum.

So now I have 30K users that I need to assign a "member" role. Is there a way to add this role to all users at once?


  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP

    It's possible, but you would need to do it via a DB query.

  • any chance you could help me with what that query would be?

  • Here is a fine addon, but it needs to be (perhaps) slightly updated to get to the options page

  • BleistivtBleistivt Moderator

    Here is a fork that has been adapted to the new dashboard layout:

  • QuethQueth New
    edited March 2019

    thank you @donshakespeare and @Bleistivt ! I have added this now.

    however, it doesn't allow a *.* (eg ALL users) option. So while it will save time, I rather want to do one action for every user at once.

    or if I have overlooked it please let me know how to select ALL users with this :)

    @Kaspar I don't think YAGA is for that, that adds custom badges and things. (if it works.. I can't use it atm since it doesn't allow viewing posts when it is enabled). My question is for the member role, eg the standard role every user should have that is a member in order to view, post and such.

  • KasparKaspar Moderator

    I have not used yaga, but I tested it.

    You can create rules for when which profile should get what role.

    So I think make a rule with no requirement > award role.

    "Award Ranks which can confer different (configurable) permissions based on community perception and participation"

  • Thank you Caspar, I see what you mean. However I prefer having it set in the database with the user itself, rather than it relying on YAGA in the event YAGA doesn't work in a future update or I disable it, etc... then all those users suddenly lose their rights.

  • any luck? I think a query would solve it in one go.

    The add-on will require me to do it at least 26 times and then figure out all users who start their name with a special character. and believe me those are more than you may think.

  • KasparKaspar Moderator

    Sure thing.

    I don't know how it works but would assume it would/should store it as if the role was assigned manually ie role is kept even if yaga is removed but idk.

  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP

    Do you have a table GDN_UserRole?

    If so, what is the value in RoleID?

    If it is 0, you can do this:

    UPDATE `GDN_UserRole` SET `RoleID` = '8' WHERE `RoleID` = 0;

    As always, make sure you have a backup of your DB before trying any changes.

  • QuethQueth New
    edited March 2019

    all users are "not verified" and 'no roles'. (exeption are 100 or so users who were in groups in the old forum, and those group names are translated to roles now).

    I found the roleID of 'member' which is 32509 in GDN_Role. I don't see them in GDN_UserRole

    I can't seem to find an ID of 'not verified'.I suspect the value is 13792 since that's in almost all users data.

    so this would be it? why are these values so high?

     UPDATE `GDN_UserRole` SET `RoleID` = '32509' WHERE `RoleID` = 13792;

    yes i'll be sure to backup! :)

  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP
    edited March 2019

    Those values are certainly not default, and that query won't work, since the 2nd value isn't in the RoleID table, from what you have said.

    Ignore the 'verified', it isn't really relevant.

    You could reset the values in GDN_Role so that they match the default ones.

    2 Guest
    3 Confirm email
    4 Applicant
    8 Member
    16 Administrator
    32 Moderator

    When you look at the GDN_Role table, there are two columns, USER ID and RoleID

    If there is no value at all, try using 0 in the query for

    WHERE `RoleID` = 0;

  • QuethQueth New
    edited March 2019

    I have delved in the database and i found out that the members without a role do not show up in the GDN_Userrole table at all! Their userID from the usertable isn't in the Userrole table.

    When I assign that userID a role in the forum, that user shows up in the GDN_Userrole table with their role.

    just to be sure I ran the command you gave me, but that said as expected it had 0 rows affected.

    so I guess i need to do a query that inserts users from the usertable into userrole with the memberrole assigned? Or another way?

    Could you help?

  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP
    edited March 2019

    For full disclosure, I don't really know much about mySQL; I'm just looking on the Interwebby for likely solutions, and trying them on a test DB.

    This worked for me:

    INSERT INTO GDN_UserRole (UserID) 
    SELECT UserID 
    FROM GDN_User 

    If you have entries in the GDN_UserRole already, you will get an error re: primary keys.

    If you empty the GDN_UserRole table first, you won't get the error, BUT it will delete any existing role data. You would need to manually assign admin roles, for example.

    AfaIk, user 1 should be your superadmin, so at the least make user you know a) what role they are currently assigned, and b) you can log in with that account.

    The table will then show the users with a RoleID of 0, so you can carry out the earlier query.

    (Back upi your DB first. All suggestions carried out at user's own risk!)

  • QuethQueth New
    edited March 2019

    Thank you very much for diving into that for me :) my MySQL knowledge is very bare (as you have guessed) so this is very helpful. However I don’t want to do this approach (unless it’s last resort) since it will take a lot of time to then manually reassign roles to the users that now have them. And I, looking for the fastest solution since when I will do th actual migration I have to do all of this again but then with the real database while the forum is down. (So no new posts are lost)

    Im now doing a big test run to see what issues I need to solve before upgrading in the live environment :)

    so I will take a step back and try the other suggestions posed here (try in Yaga / use mass assign)

    or I may have to do a query in the original database before moving to put every member in a group : member, so vanilla will assign them a group when I do the convert.

Sign In or Register to comment.