SQL query for "change pwd at next login"

[Manuela Gruchmann]

Hello,

maybe someone can help us.

We are looking for a SQL statement that will check the "User must change password at next login" box on the user object. Unfortunately, nothing of the same is to be found from the table USR.

Manuela

[Andrzej Golaszewski]

The Information you're looking for is al little bit hidden but in the tabel USR. I was playing around with some testusers and after a couple of test i found out with the "User must change passweord at next login" a new date where set to USR_PwChange Column 
To select users with this setting you have to search for the date. Searching the date could a little bit tricky, to make it a little bit easier you can convert the date into a string (you can format the date and time as you need), so selecting the date ist much more easier.

With a little bit SQL Magic you can find the users with the following statement

SELECT USR_FirstName, USR_LastName, USR_PwChange FROM USR WHERE TO_CHAR(USR_PwChange,'DD-MM-YYYY') ='01-01-1960'

Hope that helps.

https://docs.automic.com/documentation/webhelp/english/ALL/components/AE/21.0/DB%20Schema/db/_structure/HTML/USR.html

[Manuela Gruchmann]

Hello Andrzej,
thanks for your effort, but I mean something else. Perhaps I have simply expressed myself incorrectly.
We once exported a user and located the field "PwdMustChange", which we want to set from "0" to "1" using SQL.

[Andrzej Golaszewski]

OK, i think i understand what you want to. At First you must identify the Dataset you want to change. I take the USR_OH_Idnr, but you can  also use every SELECT you wish to Identify your Dataset. To Modify the Checkbox you can use nearly the same as in the select query.

UPDATE USR SET USR_PwChange=TO_DATE('01.01.60','DD.MM.RR') WHERE USR_OH_Idnr='1xxxxxx'

If you execute the query, the box will be set. If you need to unset it, set it to a date in the future. NOTE it is not enoufh to set the day in past. It must be the query above to set the checkbox for the sepcified user.

Best Regards

[Manuela Gruchmann]

Hello Andrzej,
sorry for the late answer.

The statement actually does what it is supposed to do. Thanks for that. But we solved it with exporting the objects, changing the XML in appropriate place and importing.

Best regards