Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • RunnerBean 5 posts 85 karma points
    Mar 12, 2018 @ 16:18
    RunnerBean
    0

    sql to check all IsApproved setting for members

    Hi.

    After upgrading from 6 to 7+ all members are locked out because the Is Approved checkbox is not checked. (I understand this is a new property).

    Is there some sql that can be executed directly to set IsApproved setting to true for all members?

    Thanks for your time with this.

  • Owain Williams 303 posts 901 karma points MVP c-trib
    Mar 12, 2018 @ 16:35
    Owain Williams
    0

    I've not tested this but it might work:

    UPDATE dbo.umbracoUser
    SET userDisabled= '0'
    WHERE userDisabled = '1';
    
  • RunnerBean 5 posts 85 karma points
    Mar 13, 2018 @ 13:15
    RunnerBean
    0

    Thanks for the reply but the question was about members, cmsMember. I believe it is a property in the propertyData table perhaps?

  • Michaël Vanbrabandt 756 posts 2935 karma points c-trib
    Mar 13, 2018 @ 13:28
    Michaël Vanbrabandt
    0

    Hi,

    something like this:

    USE [YourDatabase]
    
    GO
    
    ;WITH DataProp as (SELECT pd.*
      FROM cmsPropertyData as pd
      JOIN cmsPropertyType as pt ON pt.Id = pd.propertytypeid AND pt.Alias = 'umbracoMemberApproved')
      UPDATE DataProp SET dataInt = 1
    

    *Make sure to backup your database first before doing such a query!

    /Michaël

  • RunnerBean 5 posts 85 karma points
    Mar 13, 2018 @ 13:49
    RunnerBean
    0

    Thanks for the reply.

    I created a view by joining the tables as you have done above and it appears that only 4 members actually have a record for this IsApproved property.

    I suppose the other 300 members did not have record created for this property.

    I am guessing I would need to also run an INSERT first setting IsApproved to true for each member that does not already have a record for IsApproved, then run an UPDATE query to change all that are currently false.

    Would this be correct or am I missing something?

  • Michaël Vanbrabandt 756 posts 2935 karma points c-trib
    Mar 13, 2018 @ 13:58
    Michaël Vanbrabandt
    0

    Hi RunnerBean,

    then the entry in the cmsPropertyData with this property for the other Members are missing.

    Don't think it's a good approach for inserting this manually because it contains a verionId of this entry also.

    What happens if you re-save one of these members that isn't fetched into the view? Does the property then appear and does your 4 becomes 5 in the view?

    /Michaël

  • RunnerBean 5 posts 85 karma points
    Mar 13, 2018 @ 14:53
    RunnerBean
    0

    Yep. So I choose a member that does not appear in the original 4, check the Is Approved property, hit save and sure enough 5 now appear in the view.

  • Michaël Vanbrabandt 756 posts 2935 karma points c-trib
    Mar 13, 2018 @ 15:07
    Michaël Vanbrabandt
    100

    Ok thats good news!

    Now you have 2 possibilities to perform this task for all your members:

    1. Manually save each Member so that this property is added into the database and is set the true
    2. Create a SurfaceController or UmbracoApiController where you create a method to perform this for you using the MemberService.

    If you need guidance for the second solution let me know.

    /Michaël

  • Owain Williams 303 posts 901 karma points MVP c-trib
    Mar 13, 2018 @ 15:11
    Owain Williams
    0

    No problems, sorry, miss understood your question. :)

    O.

Please Sign in or register to post replies

Write your reply to:

Draft