sql to check all isapproved setting for members

Go to solution

Press Ctrl / CMD + C to copy this to your clipboard.

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

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 481 posts 1413 karma points MVP 7x c-trib

Mar 12, 2018 @ 16:35
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

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 3348 karma points c-trib

Mar 13, 2018 @ 13:28

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

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 3348 karma points c-trib

Mar 13, 2018 @ 13:58

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

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 3348 karma points c-trib

Mar 13, 2018 @ 15:07
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 481 posts 1413 karma points MVP 7x c-trib

Mar 13, 2018 @ 15:11

0

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

O.

Copy Link
is working on a reply...

Please Sign in or register to post replies

Flag this post as spam?