Sign in Register
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.

    Copy Link
  • Owain Williams 432 posts 1288 karma points MVP 3x 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';
    Copy Link
  • 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?

    Copy Link
  • Michaël Vanbrabandt 863 posts 3343 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

    Copy Link
  • 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?

    Copy Link
  • Michaël Vanbrabandt 863 posts 3343 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

    Copy Link
  • 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.

    Copy Link
  • Michaël Vanbrabandt 863 posts 3343 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

    Copy Link
  • Owain Williams 432 posts 1288 karma points MVP 3x c-trib
    Mar 13, 2018 @ 15:11
    Owain Williams
    0

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

    O.

    Copy Link

  • This forum is in read-only mode while we transition to the new forum.

    You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Please Sign in or register to post replies